topic Re: Splitting records from an input table by month in QlikView

Splitting records from an input table by month

Wed, 03 Jun 2015 15:04:40 GMT

Good afternoon,

I have an input table with costs with a structure like

Year

Cost Center

Account

Value_001 (for Jan value)

Value_002 (for Feb value)

....

Value_012

I would like to split that into records like

Year

Month

Cost Center

Account

Value

How can I do this ideally using a resident load and w/o keying in code for each of the months?

Many thanks in advance,

Leonardo

stigchel — Wed, 03 Jun 2015 15:38:23 GMT

You can use something like the following (example attached)

DataTmp:

Load * Inline [Year,CostCenter,Account,Value_001,Value_002,Value_003

2014,1,A,10,20,30

2014,1,A,20,30,40

2015,1,A,30,40,50

];

DataTmp2:

CrossTable (MonthTmp,Value,3) load *

resident DataTmp;

Drop table DataTmp;

LOAD Year,CostCenter,Account,Value,MonthName('01/'&right(MonthTmp,2)&'/'&Year) as Month;

LOAD * Resident DataTmp2;

Drop table DataTmp2;