973-584-9100 contactus@bardess.com

This is a post that might be common knowledge to you, but if not this can be a crucial addition to your workflow.

ApplyMap is a function that is very similar to VLookup in Excel. Essentially you can utilize a mapping table that you can bulk find and replace, provide and input and receive and return an output that matches.




Which returns


*Note the third parameter is what should be provided if there isn’t a match. By default it is the input value.

While it is a great feature, which can also be accomplished by the LookUp function, it is extremely fast. If you are working with large datasets, it is usually faster to utilize multiple ApplyMaps than to do a join. Now I have no evidence to this claim, but my hypothesis is that ApplyMap is so fast because it happens at the bit-stuffed pointer level. I would love to have a conversation with Qlik R&D around this to better understand the inner workings of the function.

One important thing to remember, the mapping happens against the first field no matter what. Field names do not matter. So always make sure your input field is the first and your output field is the second. Due to a few forgetful moments and brainless troubleshooting, I have made it a habit to always create the mapping tables with the field names input and output . And since the mapping tables are treated differently, it does not
matter if you have several mapping tables with the same name fields.

Another thing to note is that the mapping tables are dropped at the end of the script, which alleviates some cleanup.