Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Field cannot be found when loading

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?

5 Replies
lironbaram
Partner - Master III
Partner - Master III

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

Not applicable
Author


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?

Not applicable
Author

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