How to Load the cross table from Excel with 2 rows of header

[REON_LEE]

Hi All,

I'm having difficulty to load the cross table from the excel file into QlikView that having 2 rows of the header like below:

The expected result as below:

Any help is appreciated.

[raman_rastogi · Accepted solution]

Hi Reon,

Try below Script

T1:
LOAD Product,
Cat,
[Sub Cat],
Cost as Cost0,
Price as Price0,
Cost1,
Price1,
Cost2,
Price2
FROM
[C:\Users\Online exam\Downloads\CrossTablewith2Header.xls]
(biff, embedded labels, header is 1 lines, table is Data$);

T2:
CrossTable(Fact,Value,4)
LOAD 1 as dummy,* Resident T1;
DROP Table T1;

MapTable1:
LOAD Distinct
Date(Date#(F1,'MMM-YYYY'),'MMM-YYYY') as Date

FROM
[C:\Users\Online exam\Downloads\CrossTablewith2Header.xls]
(biff, embedded labels, table is Data$, filters(
Transpose()
))Where len(F1)>1;

MapTable:
mapping LOAD
RowNo()-1 as Map,
Date Resident MapTable1;
DROP Table MapTable1;

T3:
LOAD
Right( Fact,1) as Map
,*
resident T2;
DROP Table T2;

Result:
LOAD
Date,
Product,
Cat,
[Sub Cat],
Value,
PurgeChar(Fact,0123456789) as Fact;
LOAD
applymap('MapTable',Map) as Date ,*,Map Resident T3;
DROP Table T3;

[qv_testing]

Hope it helps!!

PFA,

[REON_LEE]

Dear Raman,

Your script is very useful . Thank you very much.