Unpivot cross tab tables

[yipchunyu]

	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.

[Frank_Hartmann]

see attached file.

hope this helps

[yipchunyu]

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?

[Frank_Hartmann]

Maybe like attached.

hope this helps

[yipchunyu · Accepted solution]

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;