3 Replies Latest reply: Nov 24, 2015 12:59 PM by Chris Kelley RSS

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

    Chris Kelley

      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.