Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

CrossTable from Excel with Multiple Headers

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.,,

5 Replies
sunny_talwar

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;


Capture.PNG

Not applicable
Author

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

sunny_talwar

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

Not applicable
Author

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?

MarcoWedel

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