Transform table with mulitple month columns

[alanmcgrath]

Hi,

I have a table with multiple customers and their attributes.  There are columns for each month that have the data for the category(Cost or Revenue).  I also have a Fiscal Year column and a type column(Actual or Forecast).

My issue is how do I transform the data so I can sum for the whole year for each category and type?  So for example I want to sum fiscal year 2015 Actual Revenue for Customer ABCD. 

Attached is an example of what the table looks like.  Any help is appreciated.

Thanks!

[datanibbler]

Hi Alan,

I have had this issue quite some times.

So, what you basically need to do is turn the individual fields you have (for the different months) into one field (for month_selection) and one (for the data), right?

That means some typework for you. You'll have to

- load everything first like you probably have already done;

- Do a nr. of RESIDENT LOADs, each time

     - fixing (in a WHERE clause) the month to a certain value and

     - concatenating all those RESIDENT LOADs

- At the end, you can delete the original file.

<=> Just be careful to keep that separate from the original file.

=> The result of this should be a table with a lot more records, but fewer columns

=> Voila. You have effectively transformed all those different fields with one month's data in each into a dimension
     which the user can select on.

HTH

[alanmcgrath]

Can you share an example of some script? I think I understand the concept but just not sure how to script it.

[#4 · Accepted solution]

Seems like a great place to use a crosstable load

FactTable:

CrossTable (Month,Value,5)

LOAD Customer,

     [Customer Name],

     [Fiscal Year],

     Categ,

     Type,

     Sep,

     Oct,

     Nov,

     Dec,

     Jan,

     Feb,

     Mar,

     Apr,

     May,

     Jun,

     Jul,

     Aug

FROM

QVTest.xlsx

(ooxml, embedded labels, table is Sheet1);

[alanmcgrath]

This is working great!  Thanks!