Load two header rows by Crosstable

[#1]

Hi Guys

I have one Excel with two header rows, I want to use crosstable to load into QlikView.

The attachment is the source file, and the 'target' sheet is what I want.

Does someone could guide me how to achieve it.

Thx a lot.

[devarasu07]

Hi,

Try with Stefan Solution,

How to load a crosstable with two header rows/2 attribute fields.

[#3]

Hi Devarasu R

I tried Stefan solution. However, after loding into QlikView, from table viewer, there's one row should not be existed.

I marked in Red, do you know what's wrong with my script, thx.

[stigchel · Accepted solution]

Check for empty rows e.g if Type (Column A) has text (see red below)

TMP3:

LOAD A as Type,

    applymap('MAP1',1) as Category,

    B,

    C,

    D,

    E,

    F,

    G

FROM

[16B Finance.xlsx]

(ooxml, no labels, table is '16B')

where recno() >2 and Len(A)>0;

[passionate]

Hi Ran,

PFA, solution.

Change the numeric value of date as required.

Regards,
Pankaj

[effinty2112]

Hi Xu,

We can do this in two steps. A transposition then crosstable.

Transpose:

LOAD F1 as Category,

     Date(Type,'YY/MM') as Date,

     [Application Biz],

     [DIRECT Biz],

     [Direct expense]

FROM

test.xlsx

(ooxml, embedded labels, table is test, filters(

Transpose()

));

CrossTable:

CrossTable(Type,Value,2) LOAD * Resident Transpose Where not IsNull(Date);

DROP Table Transpose;

gives this:

Type	Date	Value	Category
Application Biz	16/10	0.00	Budget
Application Biz	16/11	773.96	Budget
Application Biz	16/12	2,268.69	Budget
Application Biz	17/01	773.96	Budget
Application Biz	17/02	0.00	Evaluate
Application Biz	17/03	3,307.91	Evaluate
DIRECT Biz	16/10	0.00	Budget
DIRECT Biz	16/11	0.00	Budget
DIRECT Biz	16/12	903.14	Budget
DIRECT Biz	17/01	0.00	Budget
DIRECT Biz	17/02	0.00	Evaluate
DIRECT Biz	17/03	903.14	Evaluate
Direct expense	16/10	36.00	Budget
Direct expense	16/11	45.00	Budget
Direct expense	16/12	41.00	Budget
Direct expense	17/01	31.00	Budget
Direct expense	17/02	26.00	Evaluate
Direct expense	17/03	40.00	Evaluate

Regards

Andrew

[#7]

Hi Piet

The table is right now. Thx!

[#8]

Hi Andrew

Seems your solution is better, no need to map anything.