topic Re: CrossTable from Excel with Multiple Headers in QlikView

CrossTable from Excel with Multiple Headers

Tue, 20 Oct 2015 16:29:17 GMT

Hi All,

I am new to Qlik and I would like to understand much better in creating right Cross table aggregation.

for example. In attached sheet for year 2015.

1. I would like to display the value of every vehicle type Old Registration with Months attributes.

2. I would like to display the value of every vehicle type New Registration with Months attributes.

Please suggest.,,

Re: CrossTable from Excel with Multiple Headers

sunny_talwar — Tue, 20 Oct 2015 18:22:23 GMT

May be this:

Table:

CrossTable(Registration, Data)

LOAD F1,

[Old Registration] & '|' &[New Registration],

[Old Registration1] & '|' &[New Registration1],

[Old Registration2] & '|' &[New Registration2],

[Old Registration3] & '|' &[New Registration3],

[Old Registration4] & '|' &[New Registration4],

[Old Registration5] & '|' &[New Registration5],

[Old Registration6] & '|' &[New Registration6],

[Old Registration7] & '|' &[New Registration7],

[Old Registration8] & '|' &[New Registration8],

[Old Registration9] & '|' &[New Registration9],

[Old Registration10] & '|' &[New Registration10],

[Old Registration11] & '|' &[New Registration11]

FROM

CrosstableExercise.xls

(biff, embedded labels, header is 1 lines, table is [46251-0005$]);

Temp:

NoConcatenate

LOAD F1 as [Vehicle Type],

MakeDate(2015, RangeSum(1, KeepChar(SubField(Registration, '|', 1), '0123456789'))) as Date,

SubField(Data, '|', 1) as [Old Registration],

SubField(Data, '|', 2) as [New Registration]

Resident Table

Where Len(Trim(KeepChar(Data, '0123456789'))) > 0;

DROP Table Table;

Re: CrossTable from Excel with Multiple Headers

Tue, 20 Oct 2015 18:44:14 GMT

Hi - Thanks for posting. would be great if you can explain logic as I am still experimenting writing script statements,.

Re: CrossTable from Excel with Multiple Headers

sunny_talwar — Tue, 20 Oct 2015 21:06:40 GMT

This is more complicated script than a regular CrossTable Load, so would require some time posting a detailed response. I am home now, but will definitely post something detailed sometime tomorrow

Re: CrossTable from Excel with Multiple Headers

Tue, 20 Oct 2015 21:14:43 GMT

okay is there a simple way, as you see in Excel.

1. There are Months ( Jan, Feb.....Dec)

2. for every month there are two categories ( Old Registration, New Registration)

3. Vehicle Type is linked to Month, Category, Value.

can we do something at transformation step and make changes when we apply crosstable and give name to attribuites and data?

Re: CrossTable from Excel with Multiple Headers

MarcoWedel — Tue, 20 Oct 2015 22:51:28 GMT

Hi,

please try to use the search function of this site.

Some examples of what you could find:

multi_header_pivot_import.qvw

xls file with multiple header row

Multiple header in the excel

Multiple header from Excel into Qlikview

hope this helps

regards

Marco