topic Transform table with mulitple month columns in QlikView

Transform table with mulitple month columns

alanmcgrath — Wed, 20 May 2015 14:59:52 GMT

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!

Re: Transform table with mulitple month columns

datanibbler — Wed, 20 May 2015 15:07:02 GMT

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

Re: Transform table with mulitple month columns

alanmcgrath — Wed, 20 May 2015 15:57:50 GMT

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

Re: Transform table with mulitple month columns

Wed, 20 May 2015 16:05:42 GMT

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);

Re: Transform table with mulitple month columns

alanmcgrath — Wed, 20 May 2015 16:22:08 GMT

This is working great! Thanks!