topic Re: applymap subfield does not return na when not found in App Development

applymap subfield does not return na when not found

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

I have below mapping table for applymap subfield

[CC]:
Mapping
LOAD

    "Detail Id" as "Cost Centre",
    "LVL3 DESCR"&'|'&
    "LVL4 DESCR"&'|'&
    "LVL5 DESCR"&'|'&
    "Cost Center" as CC
FROM [lib://Fusion/Opex Mapping.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [CC tree]);

SubField(ApplyMap('CC',"Cost Centre",'#NA'),'|',1) as "LvL3",
SubField(ApplyMap('CC',"Cost Centre",'#NA'),'|',2) as "LvL4",
SubField(ApplyMap('CC',"Cost Centre",'#NA'),'|',3) as "LvL5",
SubField(ApplyMap('CC',"Cost Centre",'#NA'),'|',4) as "LOB",
ApplyMap('Period',"Period Date",'') as AcctPeriod,

I only get '#na' for applymap of "LvL3". But for the subsequent applymap, it does not return "#n/a" for unmapped item.

May I know what I have done wrong?

Re: applymap subfield does not return na when not found

Vegar — Tue, 10 Nov 2020 05:51:14 GMT

When you are getting no mapped rows then you are trying to substring '#NA'

SubField('#NA', '|', 2), SubField('#NA', '|', 3) etc. will return null()

Re: applymap subfield does not return na when not found

43918084 — Tue, 10 Nov 2020 06:11:10 GMT

Thank you very much for your guidance.

May I clarify if I should write the formula like below?

SubField(ApplyMap('CC',"Cost Centre",SubField('#NA', '|', 1)),'|',1) as "LvL3",
SubField(ApplyMap('CC',"Cost Centre",SubField('#NA', '|', 2)),'|',2) as "LvL4",
SubField(ApplyMap('CC',"Cost Centre",SubField('#NA', '|', 3)),'|',3) as "LvL5",
SubField(ApplyMap('CC',"Cost Centre",SubField('#NA', '|', 4)),'|',4) as "LOB",

I still get "#NA" for LVL3 because the mapping value is not found

I get Null for LVL4 in which the lookup value is found in the table but no mapping field.

Re: applymap subfield does not return na when not found

MayilVahanan — Tue, 10 Nov 2020 06:15:41 GMT

Hi @43918084

Try like below

,SubField(ApplyMap('CC',"Cost Centre",'#NA'),'|',1) as "LvL3",
SubField(ApplyMap('CC',"Cost Centre",'|#NA'),'|',2) as "LvL4",
SubField(ApplyMap('CC',"Cost Centre",'||#NA'),'|',3) as "LvL5"

Re: applymap subfield does not return na when not found

EvelynP — Tue, 10 Nov 2020 06:32:52 GMT

Thanks for the information

Re: applymap subfield does not return na when not found

43918084 — Tue, 10 Nov 2020 06:39:12 GMT

Thanks a lot, Mr Vahanan. It works now