loading 2 level cross tables

[#1]

Hi,

I have the attached excel files which is a 2 level cross table. First level is year and second level is quarter.

I have to load this data into QlikView as a 4 column table

Employee

Year

Quarter

Rating

Is this possible in QlikView?

Regards,

Murali

[marcus_sommer · Accepted solution]

You need to load your table twice - one for the header (this will be a mapping table for rename) and one for the data.

t1:

Crosstable(Categorie, Value)

LOAD

    A, B, C, D, E, F, G, H, I

FROM [Cross Table.xlsx] (ooxml, no labels, table is Sheet1) Where len(A) = 0;

t2:

Load Categorie, concat(Value, '|') as Column Resident t1 Group By Categorie;

    concatenate

Load * Inline [

Categorie, Column

A, Employee

];

t3:

Mapping Load * Resident t2;

t4:

LOAD

    A, B, C, D, E, F, G, H, I

FROM [Cross Table.xlsx] (ooxml, no labels, header is 2 lines, table is Sheet1);

rename fields using t3;

t5:

Crosstable(QuarterYear, Values, 1)

Load * Resident t4;

t6:

NoConcatenate Load Employee, num(mid(QuarterYear, 1, 4)) as Year, mid(QuarterYear, 6, 2) as Quarter, Values Resident t5;

drop tables t1, t2, t4, t5;

- Marcus

[#3]

Perfect...

Thansk a lot for all your help.