Multiple row as header data source

[abbynovice]

Hi,

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

				public	public	public	public	public	public	public
				US	US	US	US	US	US	US
				New York	New York	New York	New York	Colorado	Colorado	Colorado
				Feb	Jan	Dec	Nov	Oct	Sep	Aug
				2019	2019	2018	2018	2018	2018	2018
Mobile	Male	a	facebook	10	10	10	10	10	10	10
Mobile	Female	b	facebook	10	10	10	10	10	10	10
Mobile	Male	c	twitter	10	10	10	10	10	10	10
Mobile	Female	d	twitter	10	10	10	10	10	10	10
Web	Male	e	instagram	10	10	10	10	10	10	10
Web	Female	f	instagram	10	10	10	10	10	10	10

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.

[dplr-rn]

Did you check below post?
https://community.qlik.com/t5/QlikView-Documents/multi-header-pivot-import-qvw/ta-p/1487398

[abbynovice]

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

[jonathandienst · Accepted solution]

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;

[abbynovice]

Thank you for this!