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
