topic Field not found error - ApplyMap - Alias Field in QlikView

Field not found error - ApplyMap - Alias Field

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: Field not found error - ApplyMap - Alias Field

Aurelien_Martinez — Mon, 09 Jan 2017 10:47:16 GMT

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)

Re: Field not found error - ApplyMap - Alias Field

MindaugasBacius — Mon, 09 Jan 2017 11:00:49 GMT

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

Re: Field not found error - ApplyMap - Alias Field

umashankarus — Mon, 09 Jan 2017 15:01:50 GMT

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

Re: Field not found error - ApplyMap - Alias Field

umashankarus — Mon, 09 Jan 2017 15:03:50 GMT

Hi,

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

Thanks,

Umashankar

Re: Field not found error - ApplyMap - Alias Field

Aurelien_Martinez — Mon, 09 Jan 2017 16:26:35 GMT

Can you share your xlsx file ? or an example

Re: Field not found error - ApplyMap - Alias Field

umashankarus — Mon, 09 Jan 2017 16:42:33 GMT

Hi,

I have attached sample file

Thanks,

Umashankar

Re: Field not found error - ApplyMap - Alias Field

ElizaF — Mon, 09 Jan 2017 16:48:50 GMT

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

Re: Field not found error - ApplyMap - Alias Field

umashankarus — Mon, 09 Jan 2017 16:56:13 GMT

Hi,

That was a copy paste issue. Pl ignore

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

Thanks,

Umashankar

Re: Field not found error - ApplyMap - Alias Field

Aurelien_Martinez — Mon, 09 Jan 2017 17:07:32 GMT

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

Re: Field not found error - ApplyMap - Alias Field

ElizaF — Mon, 09 Jan 2017 17:17:33 GMT

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

Re: Field not found error - ApplyMap - Alias Field

umashankarus — Mon, 09 Jan 2017 19:38:35 GMT

Hi,

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 ?

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

Re: Field not found error - ApplyMap - Alias Field

umashankarus — Mon, 09 Jan 2017 20:13:00 GMT

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 !!!