applymap subfield does not return na when not found

[43918084]

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?

[Vegar]

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

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

[43918084]

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.

[MayilVahanan · Accepted solution]

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"

[EvelynP]

Thanks for the information 

[43918084]

Thanks a lot, Mr Vahanan.  It works now