Field cannot be found when loading

[#1]

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?

[lironbaram]

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

[avinashelite]

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

[#4]

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?

[#5]

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?

[avinashelite]

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';