topic Re: Unpivot cross tab tables in QlikView

Unpivot cross tab tables

yipchunyu — Thu, 08 Nov 2018 03:20:33 GMT

	Type A	Type A	Type B	Type B
	Amount 1	Amount 2	Amount 1	Amount 2
Company A	10	20	8	14
Company B	0	0	15	9

How to transform the above table as in below table format when loading the data?

Name	Type	Amount 1	Amount 2
Company A	Type A	10	20
Company A	Type B	8	14
Company B	Type A	0	0
Company B	Type B	15	9

I checked the function and it seems crosstable may help. But no concrete idea yet.

Re: Unpivot cross tab tables

Frank_Hartmann — Thu, 08 Nov 2018 08:37:08 GMT

see attached file.

hope this helps

Re: Unpivot cross tab tables

yipchunyu — Thu, 08 Nov 2018 10:34:45 GMT

Really sorry. Tried your file but can't apply to my case correctly. (field names not match)

Would you pls help to check my shared file?

Re: Unpivot cross tab tables

Frank_Hartmann — Thu, 08 Nov 2018 11:17:10 GMT

Maybe like attached.

hope this helps

Re: Unpivot cross tab tables

yipchunyu — Fri, 09 Nov 2018 03:54:47 GMT

Thanks and it's a big step closer to my expected results.

I further revise the codes by categories the amount by the types as below.

Many thx.

CrossRaw:

CrossTable(COMPANY_NAME, AMOUNT, 2)

LOAD *

FROM

[..\Raw\raw_revised.xlsx]

(ooxml, embedded labels, table is RAW_2018Q1, filters(

Transpose()

));

Raw:

Load

COMPANY_NAME,

QUARTER,

TYPE,

SUM(SP_HKD) AS SP_HKD,

SUM(NON_SP_HKD) AS NON_SP_HKD,

SUM(SP_NOP) AS SP_NOP,

SUM(NON_SP_NOP) AS NON_SP_NOP

GROUP BY COMPANY_NAME, QUARTER, TYPE;

LOAD

//ApplyMap('MapCompany', COMPANY, null()) AS COMPANY_NAME,

COMPANY_NAME,

$(vQuarter) AS QUARTER,

TYPE,

IF(CATEGORY = 'NON_SP_HKD', AMOUNT, 0) AS NON_SP_HKD,

IF(CATEGORY = 'SP_HKD', AMOUNT, 0) AS SP_HKD,

IF(CATEGORY = 'NON_SP_NOP', AMOUNT, 0) AS NON_SP_NOP,

IF(CATEGORY = 'SP_NOP', AMOUNT, 0) AS SP_NOP

Resident CrossRaw;