Field column cannot be found in second load

Thu, 02 Feb 2017 09:58:05 GMT

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. But this is giving error because they say NEWCOLUMN cannot be found from the 2nd load. How do I fix this?

Re: Field column cannot be found in second load

sunny_talwar — Thu, 02 Feb 2017 10:12:53 GMT

If 20161231.xls doesn't include NEWCOLUMN, you cannot use this field in your load without bringing this from your BOS_MAPPING table without joining the two tables or using Lookup or ApplyMap. The field needs to be in the load of a table before you can use it for any calculations.

Best,

Sunny

topic Re: Field column cannot be found in second load in QlikView

Field column cannot be found in second load

Re: Field column cannot be found in second load