Using mapping as an alternative to joining

The Join prefix in QlikView is a powerful way of combining several data tables in the data model. One disadvantage is that the combined tables can become large and create performance problems. An alternative to Join in situations where you need to look up a single value from another table is to use mapping instead. This can save you from loading unnecessary data that slows down calculations and potentially can create calculation errors, as joins can change the number of records in the tables.

A mapping table consists of two columns; a comparison field (input) and a mapping value field (output).

In this example we have an table of orders (Orders), and need to know the country of the customer, which is stored in the customer table (Customers).

In order to look up the country (Country) of a customer, we need a mapping table that looks like this:

The mapping table, which we name MapCustomerIDtoCountry, is defined in the script as follows:

The next step is to apply the mapping, using the ApplyMap function, when loading the order table:

The third parameter of the ApplyMap function is used to define what to return when the value is not found in the mapping table, in this case Null().

The resulting table will look like this: