ApplyMap: Keep original value if not matched (How?)

[Anonymous]

I am trying to perform the following function using ApplyMap:

1. Match values from "circuit_group" to the corresponding value in the mapping file. The second column in this file contains the corrected value for the matched "circuit_group" value (such as corrected spelling for known bad values). I have this working fine using ApplyMap.
2. However, I also want to ensure that if the value in "circuit_group" does not match the mapping file, the original value of "circuit_group" is retained.

I would have thought that for case #2 above, the following code would work, but it does not. How can I tweak this to ensure that if there is no match, Qlik simply keeps the value of "circuit_group"?

[circuit_group_map]:
MAPPING LOAD
    [circuit_group],
    [circuit_group_revised]
FROM [lib://XYZ/CIRCUIT_GROUP_MAP.xlsx]
(ooxml, embedded labels, table is map_table);

...
UPPER(APPLYMAP('circuit_group_map',circuit_group,[circuit_group])) AS [Circuit Group],
...

Adding ",[circuit_group]" above, does not preserve the non-matching values from circuit_group.

[rwunderlich]

You should be able to just omit the third parameter, and unmatched values will remain unchanged.

However, the way you've coded it by specifying circuit_group as the third parm should work as well. So I suspect there is something else going on. Can you try omitting the third parm, and if that doesn't work, can you post a sample demonstrating the problem?

-Rob

[sunny_talwar]

Seems to be working for me:

circuit_group_map:

Mapping

LOAD * Inline [

circuit_group, circuit_group_revised

abc, ABCD

def, DEFG

];

Table:

LOAD circuit_group,

  Upper(ApplyMap('circuit_group_map', circuit_group, circuit_group)) AS [Circuit Group]

Inline [

circuit_group

abc

def

ghi

jkl

mno

pqr

];

[Anonymous]

Interesting. Perhaps it is the fact I have quite a few instances of ApplyMap in the script. If I isolate to just one instance of it, the behaviors you describe are correct.

I'll keep poking around and post an update when I find the error.