Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chkelley
New Contributor III

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

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.

Tags (2)
3 Replies

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

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

MVP
MVP

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

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

];


Capture.PNG

chkelley
New Contributor III

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

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.