[SOLVED] Averaging massive sets of data

[karmacomposer]

Yep, I'm still here.

My last hurdle is to average large amounts of numbers. I am trying to use the average function, but i'm afraid I am doing it all wrong.

Code: Select all

put average(varCredLimitAtlantaCount) into varCredLimitAtlantaAvg
      put average(varCredLimitAugustaCount) into varCredLimitAugustaAvg
      put average(varCredLimitCharlestonCount) into varCredLimitCharlestonAvg
      put average(varCredLimitCharlotteCount) into varCredLimitCharlotteAvg
      put average(varCredLimitMyrtleBchCount) into varCredLimitMyrtleBchAvg
      put average(varCredLimitWashingtonCount) into varCredLimitWashingtonAvg

Each Count variable is just that - i'm adding the number to a variable - obviously this number will get very large over time (400 or so records makes it large) - these are involving income related data.

The way I am counting/adding the data is like this:

Code: Select all

##=========================================================
         
         if ccZipDecrypted = "28105" then
            add ccCreditScoreDecrypted to varCredScore28105Count
         end if
         
         if ccZipDecrypted = "28130" then
            add ccCreditScoreDecrypted to varCredScore28130Count
         end if
         
         if ccZipDecrypted = "28401" then
            add ccCreditScoreDecrypted to varCredScore28401Count
         end if
         
         if ccZipDecrypted = "28404" then
            add ccCreditScoreDecrypted to varCredScore28404Count
         end if
         
         if ccZipDecrypted = "29401" then
            add ccCreditScoreDecrypted to varCredScore29401Count
         end if
         
         if ccZipDecrypted = "29405" then
            add ccCreditScoreDecrypted to varCredScore29405Count
         end if
         		 
         if ccZipDecrypted = "29572" then
            add ccCreditScoreDecrypted to varCredScore29572Count
         end if
         
         if ccZipDecrypted = "29577" then
            add ccCreditScoreDecrypted to varCredScore29577Count
         end if
         
         if ccZipDecrypted = "30301" then
            add ccCreditScoreDecrypted to varCredScore30301Count
         end if
         
         if ccZipDecrypted = "30303" then
            add ccCreditScoreDecrypted to varCredScore30303Count
         end if
         
         if ccZipDecrypted = "30805" then
            add ccCreditScoreDecrypted to varCredScore30805Count
         end if
         
         if ccZipDecrypted = "30808" then
            add ccCreditScoreDecrypted to varCredScore30808Count
         end if
         
         ##==========================================================

I am parsing the data to ensure it's a number, has no commas, no spaces and no dollar sign. The result is decimal number (ie. 401.23)

Soooooo

What is the better way to do this? Put them in an array?

More specifically, I am throwing an error on char 1 - no such object, yet all variables are accounted for unless my tired eyes are not seeing it:

Code: Select all

      ## Do the credit account averages - 28105,28130,28401,28404,29401,29405,29572,29577,30301,30303,30805,30808
      
      put average(varCredScore28105Count) into varCredScore28105Avg
      put average(varCredScore28130Count) into varCredScore28130Avg
      put average(varCredScore28401Count) into varCredScore28401Avg
      put average(varCredScore28404Count) into varCredScore28404Avg
      put average(varCredScore29401Count) into varCredScore29401Avg
      put average(varCredScore29405Count) into varCredScore29405Avg
      put average(varCredScore29572Count) into varCredScore29572Avg
      put average(varCredScore29577Count) into varCredScore29577Avg
      put average(varCredScore30301Count) into varCredScore30301Avg
      put average(varCredScore30303Count) into varCredScore30303Avg
      put average(varCredScore30805Count) into varCredScore30805Avg
      put average(varCredScore30808Count) into varCredScore30808Avg
      
      put average(varNetWorth28105Count) into varNetWorth28105Avg
      put average(varNetWorth28130Count) into varNetWorth28130Avg
      put average(varNetWorth28401Count) into varNetWorth28401Avg
      put average(varNetWorth28404Count) into varNetWorth28404Avg
      put average(varNetWorth29401Count) into varNetWorth29401Avg
      put average(varNetWorth29405Count) into varNetWorth29405Avg
      put average(varNetWorth29572Count) into varNetWorth29572Avg
      put average(varNetWorth29577Count) into varNetWorth29577Avg
      put average(varNetWorth30301Count) into varNetWorth30301Avg
      put average(varNetWorth30303Count) into varNetWorth30303Avg
      put average(varNetWorth30805Count) into varNetWorth30805Avg
      put average(varNetWorth30808Count) into varNetWorth30808Avg
      
      put average(varAvailCash28105Count) into varAvailCash28105Avg
      put average(varAvailCash28130Count) into varAvailCash28130Avg
      put average(varAvailCash28401Count) into varAvailCash28401Avg
      put average(varAvailCash28404Count) into varAvailCash28404Avg
      put average(varAvailCash29401Count) into varAvailCash29401Avg
      put average(varAvailCash29405Count) into varAvailCash29405Avg
      put average(varAvailCash29572Count) into varAvailCash29572Avg
      put average(varAvailCash29577Count) into varAvailCash29577Avg
      put average(varAvailCash30301Count) into varAvailCash30301Avg
      put average(varAvailCash30303Count) into varAvailCash30303Avg
      put average(varAvailCash30805Count) into varAvailCash30805Avg
      put average(varAvailCash30808Count) into varAvailCash30808Avg
      
      put "28105" & tab & varCredScore28105Avg & tab & varNetWorth28105Avg & tab & varAvailCash28105Avg & cr into line 2 of field "tblCreditScoreAvg"
      put "28130" & tab & varCredScore28130Avg & tab & varNetWorth28130Avg & tab & varAvailCash28130Avg & cr into line 3 of field "tblCreditScoreAvg"
      put "28401" & tab & varCredScore28401Avg & tab & varNetWorth28401Avg & tab & varAvailCash28401Avg & cr into line 4 of field "tblCreditScoreAvg"
      put "28404" & tab & varCredScore28404Avg & tab & varNetWorth28404Avg & tab & varAvailCash28404Avg & cr into line 5 of field "tblCreditScoreAvg"
      put "29401" & tab & varCredScore29401Avg & tab & varNetWorth29401Avg & tab & varAvailCash29401Avg & cr into line 6 of field "tblCreditScoreAvg"
      put "29405" & tab & varCredScore29405Avg & tab & varNetWorth29405Avg & tab & varAvailCash29405Avg & cr into line 7 of field "tblCreditScoreAvg"
      put "29572" & tab & varCredScore29572Avg & tab & varNetWorth29572Avg & tab & varAvailCash29572Avg & cr into line 8 of field "tblCreditScoreAvg"
      put "29577" & tab & varCredScore29577Avg & tab & varNetWorth29577Avg & tab & varAvailCash29577Avg & cr into line 9 of field "tblCreditScoreAvg"
      put "30301" & tab & varCredScore30301Avg & tab & varNetWorth30301Avg & tab & varAvailCash30301Avg & cr into line 10 of field "tblCreditScoreAvg"
      put "30303" & tab & varCredScore30303Avg & tab & varNetWorth30303Avg & tab & varAvailCash30303Avg & cr into line 11 of field "tblCreditScoreAvg"
      put "30805" & tab & varCredScore30805Avg & tab & varNetWorth30805Avg & tab & varAvailCash30805Avg & cr into line 12 of field "tblCreditScoreAvg"
      put "30808" & tab & varCredScore30808Avg & tab & varNetWorth30808Avg & tab & varAvailCash30808Avg & cr into line 13 of field "tblCreditScoreAvg"

The error is on this field, so far:

Code: Select all

put "28105" & tab & varCredScore28105Avg & tab & varNetWorth28105Avg & tab & varAvailCash28105Avg & cr into line 2 of field "tblCreditScoreAvg"

That code is going into a table field, which is spelled correctly (copied and pasted right from the property inspector window).

Any coding help is more than appreciated.

Mike

[dunbarx]

Hi.

More specifically, I am throwing an error on char 1 - no such object,

The error is telling you that the field is not recognized, not that there is a problem with whatever you want to put into that field. If, from msg, you ask:

Code: Select all

select field "tblCreditScoreAvg"

what happens?

...has no commas

As for the "average" function, it works just fine, and does not care how large or how many items (comma separated) you give it. You are giving it comma separated items, right?

Craig

[karmacomposer]

Hi.

More specifically, I am throwing an error on char 1 - no such object,

The error is telling you that the field is not recognized, not that there is a problem with whatever you want to put into that field. If, from msg, you ask:

Code: Select all

select field "tblCreditScoreAvg"

what happens?

...has no commas

As for the "average" function, it works just fine, and does not care how large or how many items (comma separated) you give it. You are giving it comma separated items, right?

Craig

The table selected, no problem.

Ummmm, nope on the commas. I was just adding up all the numbers into one variable. Like I said, I don't ever use the average function and even after reading the available docs, it did not make much sense.

Of course, I am also going on 40 hours with almost no sleep to get this finished in time.

So I need to create a super long string with over 400 values separated by commas?

Mike

[dunbarx]

Hi.

I don't ever use the average function and even after reading the available docs, it did not make much sense.

Just try:

Code: Select all

on mouseUp
   answer average("1,2,333.444")
end mouseUp(

Code: Select all

More specifically, I am throwing an error on char 1 - no such object, yet all variables are accounted...

Again, if you are sure that the field exists, then which line throws the error? I assumed the first time LC had to resolve an object reference it could not locate. The error does not indicate anything other than LC cannot find an actual control on the card.

Craig

[FourthWorld]

RE averages across zip codes:

There are more than 42,000 zip codes in the US. You do not want to hand-code a means to handle each of them.

Whenever you have a collection of things you need to keep track of, and the names of the things (such as zip codes) is large or can't be known in advance, arrays are a natural fit for the problem.

Also, you can use the avg function, and it's especialy handy when you already have a delimited list to pass to it. But the formula is simple enough (sum / n) that you don't really need it, and rolling your own lets you use more compact data than accruing a long list of delimited values, just adding to a total and keeping track of how many elements have been added as you go:

Putting that together, given a field "Display" to show the result in and a field "Src" with data like this:

Code: Select all

CCLimit,ZipCode
2000,90066
5000,90031
10000,90031
6000,90066
8000,90031

...you can write a general-purpose function to handle averaging any given item across all lines in the list, e.g.:

Code: Select all

on mouseup
   put fld "src" into tSrcData
   delete line 1 of tSrcData -- field names header
   put 1 into tValueItem
   put 2 into tLabelItem
   put AveragesList( tSrcData, tValueItem, tLabelItem) into fld "Display"
end mouseup


function AveragesList pSrcData, pValueItem, pLabelItem, pInDelim, pOutDelim
   -- Delim args are option; use reasonable defaults if not provided:
   if pInDelim is empty then put comma into pInDelim
   if pOutDelim is empty then put tab into pOutDelim
   --
   -- Gather totals, keeping track of the number of records that make up the total:
   set the itemdel to pInDelim
   repeat for each line tLine in pSrcData
      get item pLabelItem of tLine
      add item pValueItem of tLine to tTotalsA[it]
      add 1 to tCountsA[it]
   end repeat
   --
   -- Assemble averages by dividing totals by counts:
   repeat for each key tKey in tTotalsA
      put tTotalsA[tKey] / tCountsA[tKey] into tAvgList[tKey]
   end repeat
   --
   -- Format for display, sorted because we're nice that way:
   combine tAvgList with cr and pOutDelim
   sort lines of tAvgList
   return tAvgList
end AveragesList

Dick Kreisel can probably make it faster, but that may suffice for data sets with thousand of lines.

RE "Object not found" error:

If you're absolutely certain the object is a field and has the name as you've spelled it, perhaps context is the thing? Try adding this just above the errant line to see if it returns what you expect:

Code: Select all

put the long name of this cd

[SparkOut]

Array is (again) the way to go here. (Also you can easily give an array to the average function, without having to make a long list, or keep a sum and count, although Richard has shown a comprehensive way to deal with this set of circumstances).

[karmacomposer]

Richard,

This is simply a demo and is only meant to show statistics for those zipcodes only. I would never hand code a zillion zipcodes.

I'll try put

Code: Select all

the long name of this cd

and see what it says

Mike

[karmacomposer]

As predicted, it told me the name of the card and path to it. Why would that help me?

Code: Select all

put "28105" & tab & varCredScore28105Avg & tab & varNetWorth28105Avg & tab & varAvailCash28105Avg & cr into line 2 of field "tblCreditScoreAvg"
      put "28130" & tab & varCredScore28130Avg & tab & varNetWorth28130Avg & tab & varAvailCash28130Avg & cr into line 3 of field "tblCreditScoreAvg"
      put "28401" & tab & varCredScore28401Avg & tab & varNetWorth28401Avg & tab & varAvailCash28401Avg & cr into line 4 of field "tblCreditScoreAvg"
      put "28404" & tab & varCredScore28404Avg & tab & varNetWorth28404Avg & tab & varAvailCash28404Avg & cr into line 5 of field "tblCreditScoreAvg"
      put "29401" & tab & varCredScore29401Avg & tab & varNetWorth29401Avg & tab & varAvailCash29401Avg & cr into line 6 of field "tblCreditScoreAvg"
      put "29405" & tab & varCredScore29405Avg & tab & varNetWorth29405Avg & tab & varAvailCash29405Avg & cr into line 7 of field "tblCreditScoreAvg"
      put "29572" & tab & varCredScore29572Avg & tab & varNetWorth29572Avg & tab & varAvailCash29572Avg & cr into line 8 of field "tblCreditScoreAvg"
      put "29577" & tab & varCredScore29577Avg & tab & varNetWorth29577Avg & tab & varAvailCash29577Avg & cr into line 9 of field "tblCreditScoreAvg"
      put "30301" & tab & varCredScore30301Avg & tab & varNetWorth30301Avg & tab & varAvailCash30301Avg & cr into line 10 of field "tblCreditScoreAvg"
      put "30303" & tab & varCredScore30303Avg & tab & varNetWorth30303Avg & tab & varAvailCash30303Avg & cr into line 11 of field "tblCreditScoreAvg"
      put "30805" & tab & varCredScore30805Avg & tab & varNetWorth30805Avg & tab & varAvailCash30805Avg & cr into line 12 of field "tblCreditScoreAvg"
      put "30808" & tab & varCredScore30808Avg & tab & varNetWorth30808Avg & tab & varAvailCash30808Avg & cr into line 13 of field "tblCreditScoreAvg"

That is the code where the error is and it is hilighting the 1st line and saying that char 1 of that line is no such object.

Specifically, the error is the following:

card "crdMain": execution error at line 1354 (chunk: no such object), char 1

The 1st line above is line 1354

Once again, makes no sense.

Mike

[dunbarx]

So I surmised that the error is occurring at the first instance of LC needing to reference a control.

And you say that

Code: Select all

select field "tblCreditScoreAvg"

actually selects that field. Hmmm. So try this, combine the two, to inch closer to the culprit:

Code: Select all

select field "tblCreditScoreAvg"
put "28105" & tab & varCredScore28105Avg & tab & varNetWorth28105Avg & tab & varAvailCash28105Avg & cr into line 2 of field "tblCreditScoreAvg"

Report back..

Watch it, Bogs.

Craig

[FourthWorld]

The purpose of checking the context was to make sure your handler wasn't attempting to operate on another card. Your stack has multiple cards, and depending on whether navigation is happening within the script the context might change. Seems it doesn't here so we can rule that out.

The error message you showed includes a card reference not present in the code listing you provided. We can help you when you provide the code related to the error message.

[bogs]

Watch it, Bogs.

You have nothing to fear, except fear itself....but I'm a good second bet!

[karmacomposer]

Turns out it was a ChartMaker thing - I named the chart incorrectly in code and it was just throwing up all kinds of random errors.

All fixed.

Mike