Using ApplyMap in Qlik Sense

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about using ApplyMap in Qlik Sense.

Great to see so many responses and correct answers again. Luckily ApplyMap seems to be a very familiar function.

The correct answer is D.

What is ApplyMap?

ApplyMap is a powerful function in Qlik Sense, making it possible to associate certain values with the contents of a field. In this question ApplyMap is used to take the values of the field ‘Country Name‘ and associate those with the field Country from another table. The Syntax for ApplyMap is as follows:

ApplyMap('map_name', expression [ , default_mapping ] )

The Mapping Table:

First off in order to properly use the ApplyMap function you need something called a mapping table. This table consists of two fields, the first containing the values you are comparing, the second containing the mapping values. As in the question we have the following table:

It is good to understand that this table only consists in the internal memory of Qlik. After running the Load Script all mapping tables are automatically dropped from the data model. A mapping table is created by using the prefix Mapping to a load or select statement. For example, the mapping table above was loaded as:

MAP_CountryCode:
MAPPING LOAD
[Countrycode]
, [Country Name]
FROM [Lib://Countries.QVD]
(qvd)
;

It is important to properly name the mapping table, since this is the identifier for the ApplyMap function.

And yes! There are more options to use a Mapping Table with. The Map….Using and Rename Field statements (read more about Map…Using here) or the Mapsubstring function. However let’s focus in this case only on ApplyMap:

Using the Mapping Table with ApplyMap

The syntax for ApplyMap consists out of three input variables. The map_name, which is the name of the Mapping Table you are referring to. The expression or field you want to have compared and a default mapping. Let’s have another look at the ApplyMap syntax below, this time compared to how it was used in this week’s question:

ApplyMap('map_name', expression [ , default_mapping ] )
ApplyMap(‘MAP_CountryCode’, Country) AS [Country Name]

We see in the ApplyMap syntax that the default mapping is optional and in the expression used in the question, the default mapping was left empty. This means that the already existing value of the evaluated field will remain. So in this case there is no match between ‘IN’ and the mapping table, resulting in the result of answer D. Now if we change the expression to include the default mapping as follows:

ApplyMap(‘MAP_CountryCode’, Country, ‘Country Unknown’) AS [Country Name]

This will return ‘Country Unknown’ when the field value ‘IN’ is evaluated. Making it possible to select this in the front-end and use this to fix data quality issues.

Benefits of ApplyMap

While loading the Fact table from this question we see that the Country field are country short codes. This is something unwanted in the front end. Therefor this needs to be replaced with the country names as found in the Countries QVD. Simple right? Just use a left join and presto: All countries are matched.

However. This means that the fact table still contains the field Country which is the short code. This might not only be confusing; having Country and Country Name together in a single model. It is redundant information. So the field has to be dropped. And what if the fact table is build up from many different tables? We might need to do more joins in the end…

This is all negated by using the ApplyMap function and a Mapping Table. Since the table is only loaded once and automatically dropped at the end of the script, there is not much overhead. It is simple to use and easy to understand. Even better, it will help with data quality. As we saw that values which are not mapped can be given a default value.

That’s it for this week!