0 Replies Latest reply: Feb 25, 2013 10:33 AM by Jeffrey Vermeire RSS

    How-To: Multi-field ApplyMap

    Jeffrey Vermeire

      I'm leading a discussion on ApplyMap at my company in an hour or so, and decided to poke around the QlikCommunity for any information that I may have missed.  I have seen several posts about difficulties using ApplyMap to return multiple fields instead of one-to-one.  With that, I offer my solution: SubField.

       

      1. When generating a map, concatenate your output fields into one with a delimiter (I prefer the pipe "|" character since you seldom see it in any kind of text)
      2. Then, when using ApplyMap, simply wrap a SubField function around it and indicate which field to return.

       

      Here's an example:

      MAP_zip:

      Mapping

      LOAD

           zip_code,

           county & '|' & state               // concatenate the county and state together using a pipe character

      FROM

           zip_code_list.qvd (qvd)

      ;

       

      Orders:

      LOAD

           Order_id,

           Customer_name,

           Zip_Code,

           SubField(ApplyMap('MAP_zip', Zip_Code), '|', 1) as County,          // return the county for the specified zip code (1st field in the map)

           SubField(ApplyMap('MAP_zip', Zip_Code), '|', 2) as State              // return the state for the specified zip code (2nd field in the map)

      FROM

           orders.qvd (qvd)

      ;

       

      Now, I'm somewhat picky about my code and would probably do something like this to reduce the number of functions that I use:

      MAP_zip:

      Mapping

      LOAD

           zip_code,

           county & '|' & state               // concatenate the county and state together using a pipe character

      FROM

           zip_code_list.qvd (qvd)

      ;

       

      Orders:

      LOAD

           Order_id,

           Customer_name,

           Zip_Code,

           SubField(tmp_zip_map, '|', 1) as County,          // return the county for the specified zip code (1st field in the map)

           SubField(tmp_zip_map, '|', 2) as State              // return the state for the specified zip code (2nd field in the map)

      ;

      LOAD

           Order_id,

           Customer_name,

           Zip_Code,

           ApplyMap('MAP_zip', Zip_Code) as tmp_zip_map          // get county and state fields from zip code map

      FROM

           orders.qvd (qvd)

      ;

       

       

      Viola!  Multi-field ApplyMap.

       

      Hope this is found useful

      -Jeff