topic Load two header rows by Crosstable in QlikView

Load two header rows by Crosstable

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Load two header rows by Crosstable

devarasu07 — Fri, 28 Jul 2017 07:48:44 GMT

Hi,

Try with Stefan Solution,

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

Re: Load two header rows by Crosstable

Fri, 28 Jul 2017 08:54:15 GMT

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.

Re: Load two header rows by Crosstable

stigchel — Fri, 28 Jul 2017 09:22:16 GMT

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,

FROM

[16B Finance.xlsx]

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

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

Re: Load two header rows by Crosstable

passionate — Fri, 28 Jul 2017 10:18:16 GMT

Hi Ran,

PFA, solution.

Change the numeric value of date as required.

Regards,
Pankaj

Re: Load two header rows by Crosstable

effinty2112 — Fri, 28 Jul 2017 10:59:57 GMT

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

Re: Load two header rows by Crosstable

Mon, 31 Jul 2017 00:58:58 GMT

Hi Piet

The table is right now. Thx!

Re: Load two header rows by Crosstable

Mon, 31 Jul 2017 06:32:07 GMT

Hi Andrew

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