Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
BOS_MAPPING:
LOAD Line,
IF(SubStringCount([OCBC Code], '.') = 2, 'L3' , IF(SubStringCount([OCBC Code], '.') = 1, 'L2', IF(SubStringCount([OCBC Code], '.') = 0, 'L1', 'NA'))) AS NEWCOLUMN,
FROM
[..\..\SOURCE\LDR\BOS.xlsx]
(ooxml, embedded labels, table is Sheet1);
LOAD
IF(NEWCOLUMN = 'L1', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'), LOOKUP(SubField([OCBC Code],'.',1),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL1 ,
IF(NEWCOLUMN = 'L2', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'), IF(NEWCOLUMN = 'L3', LOOKUP(SubField([OCBC Code],'.',1)&'.'&SubField([OCBC Code],'.',2),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'))) as LEVEL2,
IF(NEWCOLUMN = 'L3', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL3
FROM
[..\..\SOURCE\LDR\BOS.xlsx]
(ooxml, embedded labels, table is Sheet1);
i created a new column called NEWCOLUMN in my first load. then I used that NEWCOLUMN to do some conditions. However, the script says that NEWCOLUMN cannot be found. How do I fix this error?
change your script to
BOS_MAPPING:
LOAD Line,
IF(SubStringCount([OCBC Code], '.') = 2, 'L3' , IF(SubStringCount([OCBC Code], '.') = 1, 'L2', IF(SubStringCount([OCBC Code], '.') = 0, 'L1', 'NA'))) AS NEWCOLUMN,
FROM
[..\..\SOURCE\LDR\BOS.xlsx]
(ooxml, embedded labels, table is Sheet1);
LOAD
IF(NEWCOLUMN = 'L1', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'), LOOKUP(SubField([OCBC Code],'.',1),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL1 ,
IF(NEWCOLUMN = 'L2', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'), IF(NEWCOLUMN = 'L3',LOOKUP(SubField([OCBC Code],'.',1)&'.'&SubField([OCBC Code],'.',2),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'))) asLEVEL2,
IF(NEWCOLUMN = 'L3', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL3
Resident BOS_MAPPING
You have create the NEWCOLUMN filed in Qlikview and not in the source file ..
so for the second table take 1st table as the source that will work
use the resident keyword to achieve the same
BOS_MAPPING:
LOAD Line,
COAName,
Appl,
Description,
[Line Appl],
[OCBC Code],
IF(SubStringCount([OCBC Code], '.') = 2, 'L3' , IF(SubStringCount([OCBC Code], '.') = 1, 'L2', IF(SubStringCount([OCBC Code], '.') = 0, 'L1', 'NA'))) AS NEWCOLUMN,
[OCBC Description],
[Date Change in Year 2015],
[Comments for changes in Yr 2015]
FROM
[..\..\SOURCE\LDR\BOS.xlsx]
(ooxml, embedded labels, table is Sheet1);
LOAD PARTITION_KEY,
COMPANY_CODE,
GL_ACCOUNT_CODE,
IF(NEWCOLUMN = 'L1', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'), LOOKUP(SubField([OCBC Code],'.',1),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL1 ,
IF(NEWCOLUMN = 'L2', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'), IF(NEWCOLUMN = 'L3', LOOKUP(SubField([OCBC Code],'.',1)&'.'&SubField([OCBC Code],'.',2),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'))) as LEVEL2,
IF(NEWCOLUMN = 'L3', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL3 ,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
ACCOUNT_NAME,
PARENT_RANGE,
GL_ACC_FR,
GL_ACC_TO,
GL_PDT_CODE,
SIGN,
CURRENCY_CODE,
DEBIT_CREDIT,
BALANCE,
EXCH_RATE,
UNADJ_BAL_SGD,
ADJ_BAL_SGD,
REMARK
FROM
[..\..\SOURCE\LDR\20161231.xls]
(biff, embedded labels, table is Sheet1$) where COMPANY_CODE = 'BS_LIQ_D';
Hi this is actually my 2 loads. the 2nd table is actually loading many other different column from the first table. I just want to use the newly created column in my 2nd table. How do I do that specifically?
BOS_MAPPING:
LOAD Line,
COAName,
Appl,
Description,
[Line Appl],
[OCBC Code],
IF(SubStringCount([OCBC Code], '.') = 2, 'L3' , IF(SubStringCount([OCBC Code], '.') = 1, 'L2', IF(SubStringCount([OCBC Code], '.') = 0, 'L1', 'NA'))) AS NEWCOLUMN,
[OCBC Description],
[Date Change in Year 2015],
[Comments for changes in Yr 2015]
FROM
[..\..\SOURCE\LDR\BOS.xlsx]
(ooxml, embedded labels, table is Sheet1);
LOAD PARTITION_KEY,
COMPANY_CODE,
GL_ACCOUNT_CODE,
IF(NEWCOLUMN = 'L1', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'), LOOKUP(SubField([OCBC Code],'.',1),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL1 ,
IF(NEWCOLUMN = 'L2', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'), IF(NEWCOLUMN = 'L3', LOOKUP(SubField([OCBC Code],'.',1)&'.'&SubField([OCBC Code],'.',2),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'))) as LEVEL2,
IF(NEWCOLUMN = 'L3', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL3 ,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
ACCOUNT_NAME,
PARENT_RANGE,
GL_ACC_FR,
GL_ACC_TO,
GL_PDT_CODE,
SIGN,
CURRENCY_CODE,
DEBIT_CREDIT,
BALANCE,
EXCH_RATE,
UNADJ_BAL_SGD,
ADJ_BAL_SGD,
REMARK
FROM
[..\..\SOURCE\LDR\20161231.xls]
(biff, embedded labels, table is Sheet1$) where COMPANY_CODE = 'BS_LIQ_D';
Hi this is actually my 2 loads. the 2nd table is actually loading many other different column from the first table. I just want to use the newly created column in my 2nd table. How do I do that specifically?
If that is the case then try like this
BOS_MAPPING:
Mapping load
Primary_key_field_Name,
IF(SubStringCount([OCBC Code], '.') = 2, 'L3' , IF(SubStringCount([OCBC Code], '.') = 1, 'L2', IF(SubStringCount([OCBC Code], '.') = 0, 'L1', 'NA'))) AS NEWCOLUMN
from
[..\..\SOURCE\LDR\BOS.xlsx]
(ooxml, embedded labels, table is Sheet1);
Then in the second table
replace all your NEWCOLUMN with applymap('BOS_MAPPING',Comman_key_field name')
LOAD PARTITION_KEY,
COMPANY_CODE,
GL_ACCOUNT_CODE,
IF(NEWCOLUMN = 'L1', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'),LOOKUP(SubField([OCBC Code],'.',1),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL1 ,
IF(applymap('BOS_MAPPING',Comman_key_field name')= 'L2', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'), IF(applymap('BOS_MAPPING',Comman_key_field name')= 'L3',LOOKUP(SubField([OCBC Code],'.',1)&'.'&SubField([OCBC Code],'.',2),'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING'))) asLEVEL2,
IF(applymap('BOS_MAPPING',Comman_key_field name')= 'L3', LOOKUP([OCBC Code],'Line',left(GL_ACCOUNT_CODE, 4),'BOS_MAPPING')) as LEVEL3 ,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
ACCOUNT_NAME,
PARENT_RANGE,
GL_ACC_FR,
GL_ACC_TO,
GL_PDT_CODE,
SIGN,
CURRENCY_CODE,
DEBIT_CREDIT,
BALANCE,
EXCH_RATE,
UNADJ_BAL_SGD,
ADJ_BAL_SGD,
REMARK
FROM
[..\..\SOURCE\LDR\20161231.xls]
(biff, embedded labels, table is Sheet1$) where COMPANY_CODE = 'BS_LIQ_D';