Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
abbynovice
New Contributor II

Multiple row as header data source

Hi,

I have a data source with structure like this(Please ignore the nonsense data):

    publicpublicpublicpublicpublicpublicpublic
    USUSUSUSUSUSUS
    New YorkNew YorkNew YorkNew YorkColoradoColoradoColorado
    FebJanDecNovOctSepAug
    2019201920182018201820182018
MobileMaleafacebook10101010101010
MobileFemalebfacebook10101010101010
MobileMalectwitter10101010101010
MobileFemaledtwitter10101010101010
WebMaleeinstagram10101010101010
WebFemalefinstagram10101010101010

 

Could anybody help how i can crosstable this kind of data? I tried looking for other but all I got is up to 2 row header. I'm really new to qlikview.


Thank you for those who will make time to answer.

 

Labels (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Multiple row as header data source

Here's a solution. Its hardcoded, I know, but if you just need something that works, use this. Rename the field names as required:

// Set up mapping of multiple header lines (transposed load)
MAP_HEADERS:
Mapping 
LOAD Chr(Ord('A') + RowNo() - 1) as Key,
	@1 & '|' & @2 & '|' & @3 & '|' & @4 & '|' & @5 as KeyValue
;
LOAD @1, 
     @2, 
     @3, 
     @4, 
     @5
FROM [D:\Data\MultiLevel Header.xlsx]
(ooxml, no labels, table is Sheet1, filters(Transpose()));

// Cross table load unpacks the multiple column headers and unpivots the data
T_CROSS:
CrossTable(Column, Value, 4)
LOAD A as Platform, 
     B as Gender, 
     C as Category, 
     D as App, 
     E, 
     F, 
     G, 
     H, 
     I, 
     J, 
     K
FROM
[D:\Data\MultiLevel Header.xlsx]
(ooxml, no labels, header is 5 lines, table is Sheet1);

// Construct final table
RESULT:
LOAD Platform, 
	Gender, 
	Category, 
	App,
	SubField(ColumnEx,'|', 1) as Network,
	SubField(ColumnEx,'|', 2) as Country,
	SubField(ColumnEx,'|', 3) as State,
	SubField(ColumnEx,'|', 4) as Month,
	Num(SubField(ColumnEx,'|', 5)) as Year,
	Date(Date#(SubField(ColumnEx,'|', 4) & '.' & Num(SubField(ColumnEx,'|', 5)), 'MMM.yyyy'), 'yyyy.MM') as Period,
	Value;
LOAD Platform, 
	Gender, 
	Category, 
	App,
	ApplyMap('MAP_HEADERS', Column) as ColumnEx,
	Value
Resident T_CROSS;

DROP Table T_CROSS;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
Partner
Partner

Re: Multiple row as header data source

abbynovice
New Contributor II

Re: Multiple row as header data source

Yes.I already checked on that. I tried that but I cant make it work. Im really new in qlikview development
MVP
MVP

Re: Multiple row as header data source

Here's a solution. Its hardcoded, I know, but if you just need something that works, use this. Rename the field names as required:

// Set up mapping of multiple header lines (transposed load)
MAP_HEADERS:
Mapping 
LOAD Chr(Ord('A') + RowNo() - 1) as Key,
	@1 & '|' & @2 & '|' & @3 & '|' & @4 & '|' & @5 as KeyValue
;
LOAD @1, 
     @2, 
     @3, 
     @4, 
     @5
FROM [D:\Data\MultiLevel Header.xlsx]
(ooxml, no labels, table is Sheet1, filters(Transpose()));

// Cross table load unpacks the multiple column headers and unpivots the data
T_CROSS:
CrossTable(Column, Value, 4)
LOAD A as Platform, 
     B as Gender, 
     C as Category, 
     D as App, 
     E, 
     F, 
     G, 
     H, 
     I, 
     J, 
     K
FROM
[D:\Data\MultiLevel Header.xlsx]
(ooxml, no labels, header is 5 lines, table is Sheet1);

// Construct final table
RESULT:
LOAD Platform, 
	Gender, 
	Category, 
	App,
	SubField(ColumnEx,'|', 1) as Network,
	SubField(ColumnEx,'|', 2) as Country,
	SubField(ColumnEx,'|', 3) as State,
	SubField(ColumnEx,'|', 4) as Month,
	Num(SubField(ColumnEx,'|', 5)) as Year,
	Date(Date#(SubField(ColumnEx,'|', 4) & '.' & Num(SubField(ColumnEx,'|', 5)), 'MMM.yyyy'), 'yyyy.MM') as Period,
	Value;
LOAD Platform, 
	Gender, 
	Category, 
	App,
	ApplyMap('MAP_HEADERS', Column) as ColumnEx,
	Value
Resident T_CROSS;

DROP Table T_CROSS;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
abbynovice
New Contributor II

Re: Multiple row as header data source

Thank you for this!