Convert time-across data to time-down

[#1]

Customer is providing source data where the amounts are separated by month in columns. For example, I have a column for Jan-09, one for Feb-09, and so on. When I load the data, I'd like to convert all of the amount columns into a single column and create a "Period" column indicating the period of the record (e.g. Jan-09, Feb-09, etc.). Thoughts?

Kevin

[jonathandienst]

Kevin

Use a crosstable load. This is an option with the table files wizard on the final page of the wizard.

You should land up with something like:

CrossTable(Month, Volume)
LOAD [CustID],
 [July 09],
 [Aug 09],
 [Sep 09],
 [Oct 09],
 [Nov 09],
 [Dec 09],
 [Jan 10],
 [Feb 10],
 [Mar 10],
 [April 10],
 [May 10],
 [Jun 10]
FROM
tmpStore.txt....

This script produces a table with a columns: CustID, Month, Volume - which is what you are looking for, I think.

Jonathan