Discussion Board for collaboration on QlikView Scripting.
How to transform the above table as in below table format when loading the data?
I checked the function and it seems crosstable may help. But no concrete idea yet.
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.
CrossTable(COMPANY_NAME, AMOUNT, 2)
(ooxml, embedded labels, table is RAW_2018Q1, filters(
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;
//ApplyMap('MapCompany', COMPANY, null()) AS COMPANY_NAME,
$(vQuarter) AS QUARTER,
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
View solution in original post
see attached file.
hope this helps
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?
Maybe like attached.