Apply Map but return 'Unknown' if the result is not mapped

[#1]

Hi everyone,

I want to use apply map between a field that contains codes ABC, EFG, HIJ to another table which then matches the code to a name

But the originaly main table has ABD, EFH, HIK for instance as well as the above.

How can I use apply map to map the correct field values, but return 'Unknown' if it doesn't?

Code value

ABC Product1

ABD Unknown

etc

Many thanks

[bismart]

MapTable:

Mapping Load

Col1 as x,

Col2 as y

from .......

Use following syntax in script...

applymap('MapTable', Code, 'Unknown') as CodeDescription

[#3]

Hi, excellent, that overcomes the problem.

But now if the code is blank / null it shows 'unknown'. This is expected,

So, how can I say if the code field is blank return blank (null) and if the code is not matched to the mapped table, return 'Unknown'.

thanks.

[#4 · Accepted solution]

something like

if(len(code)=0, 0, applymap('MapTable', Code, 'Unknown')) as CodeDescription

[#5]

Fantastic, many thanks to you both for your help. 🙂