Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
What is a Mapping Load
Mapping Load is definitely one of those essential tools for your QlikView and Qlik Sense scripting. Similar to a left join, but different in distinct ways that allow you to do cool things in your QlikView scripts that would be difficult to do or would otherwise involve many steps.
Basically a mapping load allows you to “map in” a set of values to a recipient table based on a key field.
We will first look at the properties of these functions and examine the syntax. Then we will review 8 different applications of this to help you understand where these functions are a good fit.
The script below utilizes QlikView in this case, but we can apply these scripting techniques directly to Qlik Sense as well with the exception of the “from” statements which get changed to library connections.
Highlights of Mapping Load and ApplyMap
- Mapping loads come in two parts. First, a mapping table must be created. Then the map can be applied.
- Maps always consist of two fields. The first field is the key field that will be used to make the translation (the ID). The second field contains the values to be mapped.
- Once a map is created, it is held in memory until the script finishes and is then dropped. It can be reused as many times as needed in the script.
- When a map is applied, only the first encountered value from the mapped table is brought over.
- You can specify an optional value if no match is found for the mapped table.
Syntax for Mapping Load and ApplyMap
We first must create the map. Simply use a load prefix of “Mapping” to indicate this is a mapped table
- Note again that this is considered a temporary table in Qlik and will be dropped automatically at the end of the script.
- Also be aware that the first field is ALWAYS the key and the second field is ALWAYS the mapped value. The reason I emphasize this is because it is not important what the field names are called. Only the order matters.
- As long as the map table appears before we apply it, the map table can be anywhere in the script. I usually position it on the same script tab above the table I will be applying it to, but when I have maps that are applied several times, I might put that map load in script tab towards the front.
Then we can apply the map wherever needed. We will use the ApplyMap function where the first parameter is the map name (in single quotes), the second parameter is the key we use to translate the map from the current table and an optional third parameter will define a value when no match is found.
- A map can be applied multiple times within a script.
- We must use single quotes when referring to the map name.
- Note also that the key in the receiving table does not actually need to loaded as a separate field. In the example above, CustomerID has simply been commented out since we do not need to display this field in the app. We use it to translate the map but that is all we need it for.
Use Case 1 – Avoid Duplication in Fact Tables
Our central table is sacred. If we need to sum up sales or count rows or calculate any sort of metric, this is likely your fact table. It is important that maintain the integrity of this table and do not inadvertently add rows to it since that would mess up your calculations.
But there are times when we need to join data to this table. You might think that a left join is the way to do this. But what if the fields we use to join this table to our fact table actually have repeats? Notice in the charts below, when we do a left join, it will actually add records to the fact table. Now our totals are exaggerated for that customer.
If instead we do a mapping load, we ensure that only one of the ID records actually maps into the receiving table. In fact, the first record “Walmart” is mapped in and the second value “Wal-Mart” is discarded only because the load will encounter the “Walmart” value first in the current ordering of the map table.
Use Case 2 – Slowly Changing Dimensions – Only the Latest Value
This trait of mapping loads is also handy when we only want one value from the map but we want a specific value. Similar to the scenario above, if Walmart had changed their name at some point, we might want only the latest name. If we sort the table by the “Date Modified” field in descending order, we will only get the latest value. This can be helpful for mapping in slowly changing dimensions where we only want one of the values.
Use Case 3 – Easier to Manage than “Nested If’s”
Let’s consider a scenario where we have survey results on a numbered scale from 1-5. It might be handy to add the description for the numbers. We could create a “nested if” statement to handle this. This will work perfectly fine, but as this list gets larger, it might be difficult to maintain.
It might be better to map these values in, especially as the number of values in the list increases. Because the values can be provided in a table, this process becomes much easier to maintain as the number of distinct values grow.
Use Case 4 – Apply a Map and Do Math in the Same Step
Often times we must do a calculation within our script like “Quantity * Price as Extended_Amount”. But what about when the calculation we need must occur between fields that are not in the same table?
A mapping load can provide a quick answer here as well. The benefit here is that we can map the value in AND do the math in the same step. Without the mapping load function, we would be forced to join the second field to the first table and then reload the whole table using resident load.
In the example below, we need to come up with an extended cost of goods sold. But the standard cost of the item is retained in the item master, a separate table from our sales table. We will apply the cost to the sale record and do the calculation at the same time.
Use Case 5 – Reuse a Map Multiple Times
Often times, we will encounter an “attributes” table. This is a catch-all table that holds all textual descriptions for many ID fields. This single table might hold item descriptions, product families, reporting segments, etc. These tables have at least 3 columns: 1 for the value ID, 1 for the field ID and the text descriptor.
In this case, we can load this table as a map and then refer to it multiple times, wherever needed throughout our script, loading these text descriptions into appropriately named fields in our data model. Notice that we are concatenating the ValueID and the FieldID in the map and then using both fields in the map application as well. This ensures that if a ValueID has a repeat, we are not accidentally pulling text for a different field.
Below, we are applying the map once for our item description and then again for our category description.
Use Case 6 – Reference a Default Value When No Match Is Found
Often times, a business requirement will state that rather than a null value, we would like to see “No Description” for an item or “n/a” for example.
If you need to define a default value when there is no match, we can provide this value as a third parameter in the applymap function.
This example shows the simple use of a “no description” label when no match is found.
Use Case 7 – Reference a Backup Map When No Match Is Found
This is my favorite use-case for mapping loads. I have definitely had the situation where a client has said, “Well, first try find a match from this SQL table, but if there is no match, then try this spreadsheet, and if there is still no match, put n/a”.
Again, using the applymap third parameter, we can nest a backup applymap in the third parameter to meet this requirement. We could have an endless list of maps to satisfy the requirement, although I have never gone beyond three levels!
In this example, we will first go to the SQL table, then to a spreadsheet as a backup and then if there is still no match, we will add a manual description of “no description”.
Use Case 8 – Using a Map with Concatenated Keys
There might be times when our key needs to be a little more complex. Sometimes two or three fields are needed to key the mapped values to your receiving table, but as you recall, the mapping table can only have two fields.
In these cases, you can create one field that combines the keys. Just remember that you will have to create the same key when you apply the map. Learn Qlikview.
Source: Living Qlikview
