Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
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
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?

Frank_Hartmann
Honored Contributor II

Re: Unpivot cross tab tables

Maybe like attached.

hope this helps

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