Page 1 of 1
Calculations in a data grid
Posted: Wed Jan 28, 2015 10:59 am
by 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.
Re: Calculations in a data grid
Posted: Wed Jan 28, 2015 1:39 pm
by 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
Re: Calculations in a data grid
Posted: Wed Jan 28, 2015 3:54 pm
by 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
Re: Calculations in a data grid
Posted: Wed Feb 04, 2015 11:39 am
by 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.
Re: Calculations in a data grid
Posted: Wed Feb 04, 2015 1:14 pm
by keram
Hi Scoperzor,
Klaus wrote: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
Re: Calculations in a data grid
Posted: Wed Feb 04, 2015 1:16 pm
by Klaus
Please show us your script that overwrites data instead of appending them!
Re: Calculations in a data grid
Posted: Wed Feb 04, 2015 1:29 pm
by 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.
Re: Calculations in a data grid
Posted: Wed Feb 04, 2015 2:31 pm
by 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