Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
yipchunyu
New Contributor III

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
Highlighted
yipchunyu
New Contributor III

Re: Unpivot cross tab tables

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
Highlighted
Frank_Hartmann
Honored Contributor II

Re: Unpivot cross tab tables

see attached file.

Unbenannt.png

hope this helps

yipchunyu
New Contributor III

Re: Unpivot cross tab tables

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?

Highlighted
Frank_Hartmann
Honored Contributor II

Re: Unpivot cross tab tables

Maybe like attached.

hope this helps

Highlighted
yipchunyu
New Contributor III

Re: Unpivot cross tab tables

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