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
