topic Re: Multiple row as header data source in QlikView

Multiple row as header data source

abbynovice — Sat, 16 Nov 2024 04:20:50 GMT

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.

Re: Multiple row as header data source

dplr-rn — Mon, 25 Feb 2019 21:06:02 GMT

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

Re: Multiple row as header data source

abbynovice — Mon, 25 Feb 2019 23:44:59 GMT

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

Re: Multiple row as header data source

jonathandienst — Tue, 26 Feb 2019 05:44:19 GMT

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;

Re: Multiple row as header data source

abbynovice — Fri, 08 Mar 2019 15:47:34 GMT

Thank you for this!