Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
It's the same error as before.
for that reason i use 2 load
DATA:
load ...
load ...
Fusion:
load *,ApplyMap('GRCA', CCode,'Null') As GRCA;
LOAD *,NOMINAL&'_'&AFFILIATE as CCode,
Month(...
...
When I use 2 load line, it only returns the field GRCA, all other fields disappeared.
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.