Applymap error field not found

[43918084]

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

[Taoufiq_Zarra · Accepted solution]

@43918084 

Applymap('GRCA', NomAff)

ApplyMap does not recognize this field yet (NomAff)

May be u can use :

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

[43918084]

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

[43918084]

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

[Taoufiq_Zarra]

@43918084 

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

[43918084]

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

[Taoufiq_Zarra]

@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
];

[43918084]

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.

[Taoufiq_Zarra]

@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 .

[43918084]

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.