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)

[Aurelien_Martinez]

Hi,

Try :

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]&Pack, 'NoMatch') as PLUS_PACK_BASED_MATCH_SSIC

FROM

[File1.xlsx]

(ooxml, embedded labels, table is Sheet1)

[MindaugasBacius]

Please take a look if the CONCAT function is used correctly.

[umashankarus]

Hi,

I tired the one that you suggested

This time the same error..

Field not found - Pack

LOAD [Product-Code],

     [CASE-UPC],

     [CASEUPC],

     [ITEM-UPC],

    ........

    .......

Thanks,

Umashankar

[umashankarus]

Hi,

CONCAT is correct and the table (TABLE1) data loaded correctly with expected concatenated value ..when 'apply map' is commented

Thanks,

Umashankar

[Aurelien_Martinez]

Can you share your xlsx file ? or an example

[umashankarus]

Hi,

I have attached sample file

Thanks,

Umashankar

[ElizaF]

You have an extra comma at line 8 .

Instead of:

Directory;

LOAD [Product-Code],

    [CASE-UPC],

    ....

    [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);

Try:

Directory;

LOAD [Product-Code],

    [CASE-UPC],

    ....

    [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);

[umashankarus]

Hi,

That was a copy paste issue. Pl ignore

The reported error is faced inspite of 'LOAD' statements with correct comma

Thanks,

Umashankar

[Aurelien_Martinez]

LOAD [Product-Code],

     [CASE-UPC],

     CASEUPC,

     [ITEM-UPC],

     Brand,

     Description,

     Pack,

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

     ApplyMap ('MAP2',  [CASEUPC]&Pack, 'NoMatch') as PLUS_PACK_BASED_MATCH_SSIC

FROM

C:\Users\AMartinez.STORENEXT\Downloads\File1.xlsx

(ooxml, embedded labels, table is Sheet1);