Field not found error - ApplyMap - Alias Field

[umashankarus]

Hi Experts

"Field not found" error is thrown for one of the applymap where alias [CASEUPC_AND_PACK]is used.

Error is not faced for [CASEUPC]

Can you pl advise regarding the problem and fix here ?

Script:

SQL SELECT distinct UPC_COMMODITY, UPC_MFG,UPC_CASE, PACK, SEGMENT1,ITEM_MASTERCASE,

CONCAT(CONCAT(upc_commodity, upc_mfg), upc_case) as CASEUPC,

CONCAT(CONCAT(CONCAT(upc_commodity, upc_mfg), upc_case),PACK) as CASEUPC_AND_PACK

FROM TABLE1

MAP1:

Mapping LOAD CASEUPC, SEGMENT1 RESIDENT TABLE1;

MAP2:

Mapping LOAD CASEUPC_AND_PACK, SEGMENT1 RESIDENT TABLE1;

Directory;

LOAD [Product-Code],

     [CASE-UPC],

     [CASEUPC],

     [ITEM-UPC],

     Brand,

     Description,

     Pack,

     SubField(Pack,'/',1) as PACK,

     [CASEUPC]&Pack as CASEUPC_AND_PACK,

     ,ApplyMap ('MAP1', [CASEUPC], 'NoMatch') as CASEUPC_BASED_MATCH_SSIC

     ,ApplyMap ('MAP2', [CASEUPC_AND_PACK], 'NoMatch') as PLUS_PACK_BASED_MATCH_SSIC

FROM

[File1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Error:

Field not found - <CASEUPC_AND_PACK>

LOAD [Product-Code],

     [CASE-UPC],

     [CASEUPC],

     [ITEM-UPC],

     Brand,

     Description,

     Pack,

     SubField(Pack,'/',1) as PACK,

     [CASEUPC]&Pack as CASEUPC_AND_PACK,

     ,ApplyMap ('MAP1', [CASEUPC], 'NoMatch') as CASEUPC_BASED_MATCH_SSIC

     ,ApplyMap ('MAP2', [CASEUPC_AND_PACK], 'NoMatch') as PLUS_PACK_BASED_MATCH_SSIC

FROM

[File1.xlsx]

(ooxml, embedded labels, table is Sheet1)

[ElizaF · Accepted solution]

If you said that.

Try, in this case,  with preceding load

Directory;

LOAD

*

     ,ApplyMap ('MAP1', [CASEUPC], 'NoMatch') as CASEUPC_BASED_MATCH_SSIC

     ,ApplyMap ('MAP2', [CASEUPC_AND_PACK], 'NoMatch') as PLUS_PACK_BASED_MATCH_SSIC

;

Directory;

LOAD [Product-Code],

     [CASE-UPC],

     [CASEUPC],

     [ITEM-UPC],

     Brand,

     Description,

     Pack,

     SubField(Pack,'/',1) as PACK,

     [CASEUPC]&Pack as CASEUPC_AND_PACK

FROM

[File1.xlsx]

(ooxml, embedded labels, table is Sheet1);

[umashankarus]

Hi,

A)

It works for the case that you provided

[CASEUPC]&Pack as CASEUPC_AND_PACK

I am a newbie..Can you pl clarify the theory here ?

B)

But, it does not work for the case that we need ?

[CASEUPC]&PACK as CASEUPC_AND_PACK

The difference is...

>>>> PACK is a calculated field in the script = SubField(Pack,'/',1) as PACK

>>>> Pack is a persistent data column in the file

Regards,

Uma

[umashankarus]

Hi,

It worked fine with below changes in the LOAD

Directory;

LOAD

*

    ,ApplyMap ('MAP1', [CASEUPC], 'NoMatch') as CASEUPC_BASED_MATCH_SSIC

    ,ApplyMap ('MAP2', [CASEUPC_AND_PACK], 'NoMatch') as PLUS_PACK_BASED_MATCH_SSIC

;

Directory;

LOAD [Product-Code],

    [CASE-UPC],

    [CASEUPC],

    [ITEM-UPC],

    Brand,

    Description,

    Pack,

//  SubField(Pack,'/',1) as PACK,

    [CASEUPC]&(SubField(Pack,'/',1)) as CASEUPC_AND_PACK

FROM

[File1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Thanks for all the help !!!