Calculations in a data grid

[Scoperzor]

Hi again guys.

I have an existing data grid with 3 columns - Date, Value1 and Value2. I want to run calculations on the numbers in Value1 and Value2, and output the results into a new data grid under the same headings.

I'm hoping to build a few types of calculations and select which one to run via buttons (the buttons I can do myself), but I don't know how to actually perform the calculations themselves after searching around a bit.

The ones I wish to do (bearing in mind I have monthly data so each data point is for a single month):
Month-on-month % change (i.e. in Value1, Row 2/Row 1)
Year-on-year % change (i.e. in Value, Row 13/Row 1)

Thank you so much for your time.

[Klaus]

Hi Scoperzor,

you can access data from a datagrid in array notation -> the DGData of grp "your datagrid here"
Example:
...
put the dgdata of grp "your datagrid here" into tDataArray
## Add "Value1" of line 2 to "Value2" of line 1
put tDataArray[2]["Value1"] + tDataArray[1]["Value2"] into tSum
...
Use the values of the array just like single variables.
You get the picture

Be warned:
DataGrids are the most complex beasts EVER in Livecode,
so load the DataGrid docs here and learn them by heart!
http://lessons.runrev.com/m/datagrid

Everytime I have a project where datagrids are involded, I read up the complete PDF
over and over to get the grips (again)!


Best

Klaus

[dunbarx]

Hi.

What Klaus said. And especially about the DG guide.

Know also that the contents of the DG can be extracted in the clear by getting the "dgText". This comes over as an ordinary variable. But the ideas are the same. You pull out the data, work with it, and reload, either back into the original DG, or, as you want to, into another one.



Craig Newman

[Scoperzor]

Hi and thanks to both of you, Klaus and Craig.

I've made many strides over the past week with my existing data grid, but the documentation on it that you mentioned is not helping me in my next step.

I'm trying to add a column of data (which I have created an array for) to an existing data grid. Now, when I set the dgText of the data grid to the first set of data, it comes out fine. When I set it to the second set of data, it also comes out fine, but it overwrites the initial set of data.

How can I add the second set without overwriting the first? The second set relies on the first set to make calculations.

[keram]

Hi Scoperzor,

DataGrids are the most complex beasts EVER in Livecode,

You are lucky - you get at least some response from Klaus and Craig
and I'm not getting any on my Data Grid scroller problem I posted today , see how it can go with Data Grids?

keram

[Klaus]

Please show us your script that overwrites data instead of appending them!

[Scoperzor]

Code: Select all

      put "SELECT Date, CPI_New FROM tbl_Import_ZA_CPI" into query1
      put revDataFromQuery(tab, return, connID, query1) into DBData
      repeat with x =1 to the number of lines in DBData
         if x is not 1 then // To Skip first record.
            put item 2 of line x of DBData into monthDiv[x]["CPI"]
            if monthDiv[x]["CPI"] > 0 then
               put x - 1 into y
               put item 2 of line y of DBData into monthPrevCPI
               if monthPrevCPI > 0 then
                  put ((monthDiv[x]["CPI"] / monthPrevCPI)-1)*100 into monthDiv[x]["CPI m/m"]
               else
                  put 0 into monthDiv[x]["CPI m/m"]
               end if
            else
               put 0 into monthDiv[x]["CPI m/m"]
            end if
         end if
      end repeat
      set the dgProp["columns"] of group "DataGrid1" to "Date" & return & Variable1 \
            & return & "CPI m/m"
      set the dgText of group "DataGrid1" to DBData

So, the first two lines of code represent the first part of data I pull in from my database. Thereafter, the loop should be drawing on the data entries in the second column of the first set of data, and subsequently placing calculated values (monthDiv) into the third column.

I realise I haven't added the part where I try to add the new data in, but that's because everything I've tried so far does not work. I set the data grid initially with "Date" and "Variable1" columns, but after that I don't know how to add to the third column.

[Klaus]

Hi Scoperzor,

since you are setting the DGTEXT, which is just a TAB and CR delimited text list,
you should append the new "column" right in the repeat loop, note my comments:

Code: Select all

...
   put "SELECT Date, CPI_New FROM tbl_Import_ZA_CPI" into query1
   put revDataFromQuery(tab, return, connID, query1) into DBData

   ## Instead of checking "if x = 1 then next repeat!, we simply start at number 2 8-)
   repeat with x = 2 to the number of lines in DBData
      put item 2 of line x of DBData into monthDiv[x]["CPI"]
      if monthDiv[x]["CPI"] > 0 then
         put x - 1 into y
         put item 2 of line y of DBData into monthPrevCPI
         if monthPrevCPI > 0 then
            put ((monthDiv[x]["CPI"] / monthPrevCPI)-1)*100 into monthDiv[x]["CPI m/m"]
         else
            put 0 into monthDiv[x]["CPI m/m"]
         end if
      else
         put 0 into monthDiv[x]["CPI m/m"]
      end if
      
      ## Just add a new ITEM = COLUMN here:
      put TAB & monthDiv[x]["CPI m/m"] after line x of DBData
   end repeat
   set the dgProp["columns"] of group "DataGrid1" to "Date" & return & Variable1 & return & "CPI m/m"

   ## Data is correctly prepared, now we can display complete data in datagrid including new column:
   set the dgText of group "DataGrid1" to DBData
...

This way we don't even need an ARRAY here -> monthDiv[], simple variables will do in the repat loop.


Best

Klaus