Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Community Browser