Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
yipchunyu
Creator
Creator

Unpivot cross tab tables

Type AType AType BType B
Amount 1Amount 2Amount 1Amount 2
Company A1020814
Company B00159

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

NameTypeAmount 1Amount 2
Company AType A1020
Company AType B814
Company BType A00
Company BType B159

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

1 Solution

Accepted Solutions
yipchunyu
Creator
Creator
Author

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;

View solution in original post

4 Replies
Frank_Hartmann
Master II
Master II

see attached file.

Unbenannt.png

hope this helps

yipchunyu
Creator
Creator
Author

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
Master II
Master II

Maybe like attached.

hope this helps

yipchunyu
Creator
Creator
Author

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;