Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

loading 2 level cross tables

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

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

2 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

Not applicable


Perfect...

Thansk a lot for all your help.