topic Re: Applymap error field not found in App Development

Applymap error field not found

43918084 — Mon, 20 Dec 2021 21:08:46 GMT

I have below script using applymap. But I get error that Field Nomaff not found. Hope I can get some advice why on how to fix it. [GRCA]: Mapping LOAD NomAff as NomAff, grca_acct FROM [lib://Fusion/Qlik Logic.xlsx] (ooxml, embedded labels, table is [grca map]); [Fusion]: LOAD Month(ACCOUNTING_DATE)&Year(ACCOUNTING_DATE) as period, NOMINAL&'_'&AFFILIATE as NomAff, Applymap('GRCA', NomAff) as GRCA, // Below fields are originally from transaction list LEGAL_ENTITY, SERVICE, COST_CENTRE, NOMINAL, AFFILIATE, AFFILIATE_FUNCTION, ANALYSIS, SPARE1, SPARE2, FROM [lib://Fusion/GL Transaction Extract LM.xlsm] (ooxml, embedded labels, table is HBAP);

Re: Applymap error field not found

Taoufiq_Zarra — Fri, 05 Jun 2020 17:11:44 GMT

@43918084

Applymap('GRCA', NomAff)

ApplyMap does not recognize this field yet (NomAff)

May be u can use :

Applymap('GRCA', NOMINAL&'_'&AFFILIATE)

Re: Applymap error field not found

43918084 — Fri, 05 Jun 2020 21:32:25 GMT

Thank you very much for helping solve the problem I ve struggled for hours.

Re: Applymap error field not found

43918084 — Fri, 05 Jun 2020 21:47:21 GMT

I am sorry. After I run the result, I note that it does not return the second field (grca_acct). Instead, it just returns the combination of Nominal_Affiliate.

In the mapping script, I expect the result should return grca_account. Hope I can get additional advice.

[GRCA]: Mapping LOAD NomAff as NomAff, grca_acct FROM [lib://Fusion/Qlik Logic.xlsx] (ooxml, embedded labels, table is [grca map]);

Re: Applymap error field not found

Taoufiq_Zarra — Fri, 05 Jun 2020 22:16:00 GMT

@43918084

Can you share a sample data of both tables to understand the problem?

Re: Applymap error field not found

43918084 — Fri, 05 Jun 2020 22:27:22 GMT

Thank you very much again for your help. I really appreciate it. Please find below the data for mapping table [GRCA]. I combined the field in Column 1 and Column 2 as one field (Nominal_Affiliate). And I want to get the 3rd column [GRCA] in the result

[GRCA]: Mapping LOAD NOMINAL&'_'&AFFILIATE as "NomAff", grca_acct FROM [lib://Fusion/Qlik Logic.xlsx] (ooxml, embedded labels, table is [grca map]);

NOMINAL	AFFILIATE	grca_acct
5045050001	27420	PK13520
5045050001	27500	PK13520
5045050001	27730	PK13520
5045050001	0	PK12930
5047500001	0	PK13350
5051110001	0	PK80300

Below is my source data

LEGAL_ENTITY	SERVICE	COST_CENTRE	NOMINAL	AFFILIATE
29340	0	3259760020	5054445002	27420
29340	0	3259760020	5054445002	27420
29340	0	3259760020	5054445002	27420
29340	0	3259760020	5054445002	27420
29340	0	3259760020	5054445002	27420
29340	0	100211418	5052200001	0
29340	0	100211803	5052200001	0
29340	0	100211804	5052200001	0
29340	0	100211806	5052200001	0
29340	0	100210090	5052200001	0

[Fusion]: LOAD NOMINAL&'_'&AFFILIATE as "NomAff", Applymap('GRCA', "NomAff") as GRCA, // Below fields are originally from transaction list LEGAL_ENTITY, SERVICE, COST_CENTRE, NOMINAL, AFFILIATE, FROM [lib://Fusion/GL Transaction Extract LM.xlsm] (ooxml, embedded labels, table is HBAP);

Re: Applymap error field not found

Taoufiq_Zarra — Fri, 05 Jun 2020 22:51:46 GMT

@43918084

in the sample data sent there is no common data between the two tables to do the mapping.
but this script works.
I changed a little bit in the sample data to test it, see the red line, it is well mapped.

GRCA: mapping LOAD NOMINAL&'_'&AFFILIATE as CCode,grca_acct INLINE [ NOMINAL, AFFILIATE , grca_acct 5045050001, 27420, PK13520 5045050001, 27500, PK13520 5045050001, 27730, PK13520 5045050001, 0, PK12930 5047500001, 0, PK13350 5051110001, 0, PK80300 ]; DATA: load *,ApplyMap('GRCA', CCode,'Null') As GRCA; LOAD *,NOMINAL&'_'&AFFILIATE as CCode INLINE [ EGAL_ENTITY, SERVICE, COST_CENTRE, NOMINAL, AFFILIATE 29340, 0, 3259760020, 5045050001, 27420 29340, 0, 3259760020, 5054445002, 27420 29340, 0, 3259760020, 5054445002, 27420 29340, 0, 3259760020, 5054445002, 27420 29340, 0, 3259760020, 5054445002, 27420 29340, 0, 100211418, 5052200001, 0 29340, 0, 100211803, 5052200001, 0 29340, 0, 100211804, 5052200001, 0 29340, 0, 100211806, 5052200001, 0 29340, 0, 100210090, 5052200001, 0 ];

Re: Applymap error field not found

43918084 — Fri, 05 Jun 2020 23:25:36 GMT

Thank you very much again. Somehow I still get error. I have tried using individual field [Nominal] or [Affiliate] as mapping field. It works.

But when I combine the 2 fields as [Nominal_Affiliate] , the error occurs.

Re: Applymap error field not found

Taoufiq_Zarra — Fri, 05 Jun 2020 23:34:32 GMT

@43918084

Did you test the code I sent? Normally there are no errors?
If not, can you share more information so I can understand the error .

Re: Applymap error field not found

43918084 — Sat, 06 Jun 2020 00:07:02 GMT

Yes, I tried using your exact script. It works.

But when I change it to get it from the source table. The error comes out again. The loading on the mapping part is ok. The error pops up when it starts to load the source table. Please find herein the error table. Not sure if it will help. But thank you again.

Re: Applymap error field not found

Taoufiq_Zarra — Sat, 06 Jun 2020 00:14:05 GMT

It's the same error as before.

for that reason i use 2 load

DATA:

load ...

Fusion: load *,ApplyMap('GRCA', CCode,'Null') As GRCA; LOAD *,NOMINAL&'_'&AFFILIATE as CCode, Month(... ...

Re: Applymap error field not found

43918084 — Sat, 06 Jun 2020 00:39:06 GMT

When I use 2 load line, it only returns the field GRCA, all other fields disappeared.

Re: Applymap error field not found

43918084 — Sat, 06 Jun 2020 04:40:23 GMT

Thank you very much for your help. I finally get it fixed using the below script that seems to be your first advice.

Somehow I did not get the result previously.