Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
jdvermeire
Contributor

How-To: Multi-field ApplyMap

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