Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I often use some sort of mapping in the QlikView applications I create to manipulate the data. Mapping functions and statements provide developers with a way to replace or modify field values when the script is run. By simply adding a mapping table to the script, field values can be modified when the script is run using functions and statements such as the ApplyMap() function, the MapSubstring() function and the Map … using statement.
Let’s take a look at how easy it is to use mapping in a QlikView application. Assume our raw data looks like this:
You can see the country United States of America was entered in various ways. If I wanted to modify the country values so that US was used to indicate the United States of America, I could add a mapping table like this to map all the variations of the United States of America to be US.
Once I have a mapping table, I can start using it. I usually use the ApplyMap() function when I am mapping. The script below will map the Country field when this table is loaded.
The results are a table like the one below where all the Country values are consistent, even the one that was misspelled (Country field for ID 4). The mapping handled all the variations that were entered in the data source and when the mapping value was not found the default ‘US’ was used.
Now I could have also used the Map … using statement to handle the mapping. Personally, I have never used this statement but if you had many tables that loaded the Country field and you wanted to map each of them, Map … using provides an easier way of doing it with fewer changes to the script. After loading the mapping table, you can say:
...
load data
...
This will map the Country field using the CountryMap until it reached the Unmap statement or the end of the script. The main difference between this and the ApplyMap() function is with the Map … using statement, the map is applied when the field is stored to the internal table versus when the field is encountered.
One last mapping function that is available in QlikView is the MapSubstring() function that allow you to map parts of a field. Using the mapping table below, the numeric data in the Code field is replace with the text value.
Before MapSubstring() function is used:
After MapSubstring() function is used:
The numeric values in the Code field were replaced with the text values.
Mapping is a powerful feature of QlikView that I use in just about every application. It allows me to “clean up” the data and format it in a consistent manner. I often use it to help scramble data when I have many values that I need to replace with dummy data. So the next time you are editing or “fixing” the data in your data source, consider mapping. Check out the technical brief I wrote on this topic.
Thanks,
Jennell