mfstuart wrote:
Hi all,
How do I manipulate the following data list into a spreadsheet (cross-tab
report) format?
I'm assuming arrays are in the works for this to build the spreadsheet data
set,
but since I haven't worked with them, I'm not sure about that.
There is probably a faster way, or a more succinct way, using more
complex arrays - but since you said you hadn't worked with arrays much,
I kept the array use very simple. Only tricky thing (I think) is the
use of combine to convert the years array into a simple list (using the
array eliminated duplicates easily).
This assumes that the output should include all years within the range-
even if there was no data for that year. You could change that easily by
changing the lines
repeat with i = t1 down to t2
to
repeat with j = (the number of lines in tYears) down to 1
put word 1 of line i of tYears into i
on mouseUp
local theData, theMonthNames, theYear, theMonth, theValue
local firstValidYear, lastValidYear
local tYears, tData, tOutput
local W, t1, t2
put empty into field "F1"
put URL ("file:C:/Users/Alex/Documents/data.txt") into theData
put "January February March April May June July August September
October November December" into theMonthNames
set the itemDel to "-"
-- set the valid year range
put 1900 into firstValidYear
put 2009 into lastValidYear
repeat for each line theLine in theData
put item 1 of theLine into theYear
put item 2 of theLine into theMonth
put item 3 of theLine into theValue
if theYear is not a number or theYear < firstValidYear or
theYear > lastValidyear then
put "Bad year " && theLine & CR after msg
next repeat
end if
if theMonth is not among the words of theMonthNames then
put "Bad month " && theLine & CR after msg
next repeat
end if
if theValue is not a number then
put "Bad value " && theLine & CR after msg
next repeat
end if
-- add this to the list of years
put true into tYears[theYear]
-- and store the value
put theValue into tData[theMonth, theYear]
end repeat
-- now put the years into a list, and sort it
combine tYears by CR and TAB
sort tYears numeric by item 1 of each
-- and create the output
put "month" & TAB into tOutput
put word 1 of line -1 of tYears into t1
put word 1 of line 1 of tYears into t2
repeat with i = t1 down to t2
put i & TAB after tOutput
end repeat
-- delete last trailing TAb and add a newline
put CR into char -1 of tOutput
repeat for each word W in theMonthNames
put W & TAB after tOutput
repeat with i = t1 down to t2
if (W & Comma & i) is among the keys of tData then
put tData[W, i] & TAB after tOutput
else
put "0" & TAB after tOutput
end if
end repeat
put CR into char -1 of tOutput
end repeat
put tOutput into msg
end mouseUp
Hope this helps - feel free to ask if anything is unclear.
-- Alex.
_______________________________________________
use-revolution mailing list
[email protected]
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
