How-To: Multi-field ApplyMap

Anonymous — Mon, 25 Feb 2013 15:30:54 GMT

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.

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)
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

topic How-To: Multi-field ApplyMap in QlikView

How-To: Multi-field ApplyMap