Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
hope this helps
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;