[SOLVED] Something is not right with my data

[karmacomposer]

So, after eating a meal and rebooting my computer, I feel a bit better.

Now:

Code: Select all

-- double-check the results with a visual check that there are only 12 valid month name keys in the array
   put the keys of varMonthCount --or answer the keys of varMonthCount
   
   put empty into tMonthCountList --just to clear it of the other visual check data
   --we can construct a list of the month names to ensure the correct sort
   --or we can be lazy and use the built-in monthNames function
   repeat for each line tMonth in the monthNames
      put tMonth & tab & varMonthCount[(tMonth)] & cr after tMonthCountList
   end repeat
   
   put tMonthCountList into line 2 of field "tblAcctsMonth"

gives me a table with the months, but only 1 count (January 3) - in other words, none of the other months have a value.

Do I need to combine this to get the two column table to read correctly?

Line 1 is reserved for the headings. So I start at line 2

Code: Select all

## Assemble the bar chart
   put "Values=Accounts," into tValues
   repeat for each line tMonth in the monthNames
      put varMonthCount[(tMonth)] & "," after tValues
   end repeat
   delete the last char of tValues --drop the trailing comma
   put tValues into field "fldInfo"

Results in "Values=Accounts,,,,,,,,,,,,"

So no values are in the string.

What do you think is causing this?

Mike

[SparkOut]

Er, what's causing this is they way you are putting (or not putting) data into the array.
Can we see some source data and how you are taking the records and putting it into the array?

When I said "one line", you realise it is one line in a loop to iterate over your source data? At least I presume so, we really have no details of what your source data looks like. But if you have 403 genuine entries, say "January,July,August, February, August,December, July,December, May,June,April,September.....December, March" and you take each item in turn, apply that one-liner to add a counter to the key, then that's going to create yor working array. I don't have any idea what your original source looks like.

[karmacomposer]

I'm not trying to be vague, but this tech is patent pending so I am not allowed to just blurt it all out.

I have this in the main repeat loop:

Code: Select all

##=========================================================
 add 1 to varMonthCount[(ccMonthDecrypted)]
 put ccMonthDecrypted & varMonthCount[(ccMonthDecrypted)] & cr after field "fldInfo"
##==========================================================

I then used the code you kindly suggested to create the table data.

I thought the main repeat loop array, varMonthCount already has all the data in it. The field "fldInfo" is there to just show me what's
going on and it clearly shows a month and a number (though not sure why some are a 1 and sometimes the number is a 2 or 3.

Think of this like a normal database. Assume I logged in and am trying to grab some data. The table in question has 403 records and I am trying to query the "Month" column. Every record has a month between January and December, depending on when the record was created.

Don't assume I am using a database language like SQL - i'm not. Just imagine it's a database.

What I am trying to do is make a dashboard and every time the user opens it up (openCard) it tallies all records (since new records will be added eventually) and provides statistics - in this case, the number of accounts per month. It is assumed that if the month exists, it must contain a account, so all we have to do is count the number of months and put that into a number. In the table and associated bar chart, the months are obvious, so no need to dwell on those. However, the second column, the Accounts column, is the one we are trying to fill with data and therefore the new bar chart statistics.

The data is encrypted and so has to be decrypted before any processing. It is also encoded/decoded with UTF-8 on the fly. In this case, the
variable (column) in question is ccMonthDecrypted. That variable holds the month as text. Putting the result of that variable in a text box
(put ccMonthDecrypted into field "fldInfo) confirms that the decrypted text is indeed a readable Month. HOWEVER, when I try to use what
I consider to be a very normal operation - use a for/next operation to see if the variable is a certain month and if so, add 1 to a counter, it fails totally.

This is my quandry.

Now, it was suggested with the code above that I use that in the repeat loop and that fills up varMonthCount[(ccMonthDecrypted)]

So, after the repeat loop, I wish to reconstruct the array and then format the table data to work with the table (field "tblAccountMonth").

It has now been suggested that I put that into another repeat loop for the array, is that correct?

Mike

[dunbarx]

I made a button to test your early post, in order to be able to use it easily. I explicitly put some text into a variable, because I, like everyone else here, does not know how you are doing it.

Have we ever gotten past the fact that I got a "hit" for january, and you only did as well? What is good for January should be good for May.

Can you make a dummy dataSet with a dummy (but identical in structure) method of building your array? This way we can see what is going on, and you need not disclose anything proprietary.

Craig

[karmacomposer]

Here:

Code: Select all

01/22/2020;January;Blaine;Stepson;Blanchard;962-1525 Augue St.;Atlanta;Georgia;30303;USA;elit@AeneanmassaInteger.co.uk;Own;08/04/2019;169907074;475; $ 4,381.00 ; $ 4,010.00 ; $ 292,468.00 ; $ 50,403.00 ;3916130998812410;242;7708; $ 49,532.00 ; $ 8,319.00 ; $ 2,902.00 
07/07/2021;July;Celeste;Stepson;Jarvis;2009 Gravida Rd.;Atlanta;Georgia;30301;USA;id@non.edu;Rent  ;09/20/2020;169907075;245; $ 6,219.00 ; $ 8,252.00 ; $ 69,331.00 ; $ 36,655.00 ;1006833192054050;756;2567; $ 57,190.00 ; $ 7,590.00 ; $ 830.00 
04/19/2020;April;Amanda;Stepson;Knight;P.O. Box 839, 9591 Mi. Ave;Wilmington;North Carolina;28401;USA;nascetur.ridiculus@ipsum.net;Rent  ;10/17/2020;169907076;772; $ 1,431.00 ; $ 3,905.00 ; $ 953,323.00 ; $ 18,920.00 ;684214019184829;158;4026; $ 60,588.00 ; $ 5,593.00 ; $ 5,779.00 
06/26/2021;June;Iliana;Stepson;Spence;9461 Suspendisse Av.;Charlotte;North Carolina;28105;USA;convallis.ante.lectus@idblanditat.edu;Rent  ;01/08/2020;169907077;617; $ 1,771.00 ; $ 8,953.00 ; $ 785,459.00 ; $ 21,842.00 ;2082993287293610;384;3100; $ 49,021.00 ; $ 8,782.00 ; $ 3,792.00 
03/04/2021;March;Wyoming;Stepson;Kidd;8812 Nulla St.;Myrtle Beach;South Carolina;29577;USA;pede.Suspendisse.dui@Praesenteunulla.edu;Rent  ;02/22/2021;169907078;436; $ 5,291.00 ; $ 5,716.00 ; $ 230,181.00 ; $ 72,370.00 ;111647260629197;323;1638; $ 29,298.00 ; $ 2,389.00 ; $ 5,305.00 
01/15/2020;January;Belle;Stepson;Conrad;947-6223 Vitae St.;Augusta;Georgia;30805;USA;dui.semper.et@malesuada.edu;Own;11/02/2019;169907079;892; $ 6,567.00 ; $ 7,711.00 ; $ 483,712.00 ; $ 67,702.00 ;4685239575244950;225;5342; $ 42,953.00 ; $ 7,210.00 ; $ 7,302.00

This is all dummy data anyway, so none of it is real. This is a tiny subset of the 403 dummy data dataset.

The delimiter is a semicolon because we use commas.

Our tech imports this, encrypts it and so on and so on.

Assume this list is now decrypted and looks like that, kind of.

We put it into an array, item by item.

Mike

[karmacomposer]

I made a button to test your early post, in order to be able to use it easily. I explicitly put some text into a variable, because I, like everyone else here, does not know how you are doing it.

Have we ever gotten past the fact that I got a "hit" for january, and you only did as well? What is good for January should be good for May.

Can you make a dummy dataSet with a dummy (but identical in structure) method of building your array? This way we can see what is going on, and you need not disclose anything proprietary.

Craig

Yep. So far I only seem to be able to get "January | 3" in the first slot, then a cr and then all the other months but no count data.

I've tried repeat loops within the repeat loop - other arrays, etc. I just cannot make this work.

This did not work:

Code: Select all

repeat for each line tMonth in the monthNames
        put tMonth & varMonthCount[(tMonth)] & cr into finalMonthArray[tMonth]
      end repeat
	  
	  combine finalMonthArray using cr and tab
   
   put finalMonthArray into line 2 of field "tblAcctsMonth"

I have till tomorrow at 2pm to make this work - and a LOT MORE than this, so I am feeling the pressure.

Mike

[AxWald]

Hi,

I tried it this way:

Code: Select all

   put "01/22/2020;January; [...] ; $ 7,302.00" into myData
   --  = the data you posted
   
   --  preparation:
   put the monthNames into myNames
   put the short monthnames into myNums
   repeat with i = 1 to 12                                   --  prepopulate array
      put line i of myNums into myArr[(line i of myNames)]["Num"]
   end repeat
      
   --  count occurences in the example data (= myData):
   set itemdel to ";"
   repeat for each line L in myData
      add 1 to myArr[item 2 of L]["Cnt"]
   end repeat
   
   --  preparing output:
   repeat for each key K in myArr                   --  create list from array
      put myArr[K]["Num"] & comma & K & comma & myArr[K]["Cnt"] & CR after myVar
   end repeat
   delete char -1 of myVar
   set itemdel to empty
   sort lines of myVar numeric by item 1 of each     --  sort it
   put myVar                                                       --  output it

Works. Have fun!

[karmacomposer]

Er, what's causing this is they way you are putting (or not putting) data into the array.
Can we see some source data and how you are taking the records and putting it into the array?

When I said "one line", you realise it is one line in a loop to iterate over your source data? At least I presume so, we really have no details of what your source data looks like. But if you have 403 genuine entries, say "January,July,August, February, August,December, July,December, May,June,April,September.....December, March" and you take each item in turn, apply that one-liner to add a counter to the key, then that's going to create yor working array. I don't have any idea what your original source looks like.

The one line is working great. Right now I am only using about 20 records so I don't have to wait a while each time.

The problem is now taking that data and putting into a two column table - 1st column months (no real problem there), second column the counts.

The real headache here is that even with just 20 records I get twenty results. Those need to be combined somehow to get 12 results - the counts for each month.

How to do this, I just cannot wrap my brain around at this point.

Mike

[karmacomposer]

Here is the dashboard:



Forget the middle and pie charts. I am concentrating on the two tables to the left and bar charts.

Notice the Accounts per Month table only shows one value.

Here is what it should look like:



I hard coded the second one.

Mike

[karmacomposer]

Hi,

I tried it this way:

Code: Select all

   put "01/22/2020;January; [...] ; $ 7,302.00" into myData
   --  = the data you posted
   
   --  preparation:
   put the monthNames into myNames
   put the short monthnames into myNums
   repeat with i = 1 to 12                                   --  prepopulate array
      put line i of myNums into myArr[(line i of myNames)]["Num"]
   end repeat
      
   --  count occurences in the example data (= myData):
   set itemdel to ";"
   repeat for each line L in myData
      add 1 to myArr[item 2 of L]["Cnt"]
   end repeat
   
   --  preparing output:
   repeat for each key K in myArr                   --  create list from array
      put myArr[K]["Num"] & comma & K & comma & myArr[K]["Cnt"] & CR after myVar
   end repeat
   delete char -1 of myVar
   set itemdel to empty
   sort lines of myVar numeric by item 1 of each     --  sort it
   put myVar                                                       --  output it

Works. Have fun!

That is NOT the way I am collecting the data. That was just raw data from the .csv file. The data imports and is encrypted with the software.

Reading the record and decrypting it is how I get the data I am trying to count. Therefore, the decryptValue is what I am counting. The above code just won't work.

Thank you for the help, however.

Mike

[karmacomposer]

I'm getting somewhere.

This code works in populating the table correctly (put 1 into varLN is before the repeat loop - so that we start at line 2 in the table):

Code: Select all

##=========================================================
add 1 to varMonthCount[(ccMonthDecrypted)]
add 1 to ln
put ccMonthDecrypted & "  " & varMonthCount[(ccMonthDecrypted)] & cr after field "fldInfo"
put ccMonthDecrypted & tab & varMonthCount[(ccMonthDecrypted)] & cr into line ln of field "tblAcctsMonth"
##==========================================================

Again, this outputs all the months and their hits - but not 12 months and all tallied hits.

How would I tally the hits and show them in a list of the 12 months?

I am just not wrapping my head around this. In any other language I know, I could code this easily. Why is Livecode so difficult to code something this obvious? I can code really fast in Livecode until something like this!!!

Mike

[FourthWorld]

This is a tiny subset of the 403 dummy data dataset.

What is "the 403 dummy data dataset"? With a larger sample we might be able to come up with a convenient, streamlined function for you.

[karmacomposer]

Richard,

More of the same.

However, I just import that data into our system, so the data is not important.

What is important is for me to count two things right now (and the rest after):

The Accounts per month

and

The Accounts per zipcode

The two variables for that are:

ccMonthDecrypted

and

ccZipDecrypted

I just need to figure out a way to make a counter that adds 1 each time the variable is met.

So, over the 403 records, say there are a total of 32 accounts in January because out of the 403 records, the month January is in a total of 32 records.

Same thing for the zipcodes. In this case, specifically 12 zipcodes (28105,28130,28401,28404,29401,29405,29572,29577,30301,30303,30805,30808)

How would you do this?

Mike

[karmacomposer]

Richard,

Tell me something, how come this does not work in Livecode - assume ccMonthDecrypted contains a properly spelled month (ex: January):

Code: Select all

if ccMonthDecrypted = "January" then
 ## Do something
else
 ## Don't do something
end if

Why does that not work?

[karmacomposer]

In a moment of clarity, I wrote the following code in a new project with nothing loaded:

Code: Select all

-- Sent when the mouse is released after clicking
-- pMouseButton specifies which mouse button was pressed
on mouseUp pMouseButton
   
   local arrayMonths
   
   put empty into field "tblMonths"
   
   put "January" into arrayMonths[1]
   put "February" into arrayMonths[2]
   put "March" into arrayMonths[3]
   put "April" into arrayMonths[4]
   put "May" into arrayMonths[5]
   put "June" into arrayMonths[6]
   put "July" into arrayMonths[7]
   put "August" into arrayMonths[8]
   put "September" into arrayMonths[9]
   put "October" into arrayMonths[10]
   put "November" into arrayMonths[11]
   put "December" into arrayMonths[12]
   
   repeat with y = 1 to 100
      
      put random(12) into varMonth
      
      if arrayMonths[varMonth] = "January" then
         add 1 to varJanCount
      end if
      
      if arrayMonths[varMonth] = "February" then
         add 1 to varFebCount
      end if
      
      if arrayMonths[varMonth] = "March" then
         add 1 to varMarCount
      end if
      
      if arrayMonths[varMonth] = "April" then
         add 1 to varAprCount
      end if
      
      if arrayMonths[varMonth] = "May" then
         add 1 to varMayCount
      end if
      
      if arrayMonths[varMonth] = "June" then
         add 1 to varJunCount
      end if
      
      if arrayMonths[varMonth] = "July" then
         add 1 to varJulCount
      end if
      
      if arrayMonths[varMonth] = "August" then
         add 1 to varAugCount
      end if
      
      if arrayMonths[varMonth] = "September" then
         add 1 to varSepCount
      end if
      
      if arrayMonths[varMonth] = "October" then
         add 1 to varOctCount
      end if
      
      if arrayMonths[varMonth] = "November" then
         add 1 to varNovCount
      end if
      
      if arrayMonths[varMonth] = "December" then
         add 1 to varDecCount
      end if
      
   end repeat
   
   put "January" & tab & varJanCount & cr into line 2 of field "tblMonths"
   put "February" & tab & varFebCount & cr into line 3 of field "tblMonths"
   put "March" & tab & varMarCount & cr into line 4 of field "tblMonths"
   put "April" & tab & varAprCount & cr into line 5 of field "tblMonths"
   put "May" & tab & varMayCount & cr into line 6 of field "tblMonths"
   put "June" & tab & varJunCount & cr into line 7 of field "tblMonths"
   put "July" & tab & varJulCount & cr into line 8 of field "tblMonths"
   put "August" & tab & varAugCount & cr into line 9 of field "tblMonths"
   put "September" & tab & varSepCount & cr into line 10 of field "tblMonths"
   put "October" & tab & varOctCount & cr into line 11 of field "tblMonths"
   put "November" & tab & varNovCount & cr into line 12 of field "tblMonths"
   put "December" & tab & varDecCount & cr into line 13 of field "tblMonths"
   
end mouseUp

It works 100%.

So....

Not sure what is wrong.

Mike