Cross-tab report: from list

[Alex Tweedly]

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.