Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER

My latest blog post was on joins in QlikView (see To Join or not to Join). In it I claimed that you should avoid making joins in the QlikView script, if possible. This blog post is about a function that can help you avoid joins. It is about the function Applymap().

It is, in fact, one of my favorite functions and I do not seem to be alone in this choice. Several of the QlikView developers who regularly write on QlikCommunity seem to share this preference and have written about this function, e.g., QlikView Notes, Quick Intelligence and QVApps.

 

So, what does the function do? Well, basically it is just a lookup function – it takes one value as input, checks if this value exists in a mapping table and returns the corresponding value from the second column in the mapping table. Think of it as a translation function where the translation is fetched from a pre-defined, two-column table. A simple translation function, but you can still do a lot with it...

 

What has this got to do with joins? Everything. A very common situation in data modeling is that you need to get a single field value (per record) from a different table than the current one. Then the standard way to do this is to use a join. However, in QlikView you can – and should – use Applymap() instead. The only time that you cannot use Applymap() instead of a join is if you want to fetch more than one single corresponding value (record) from the second table.

 

Let's look at an example of how to use Applymap(): In an ERP system, the customer table is the table where all information about the customer is stored, e.g. the name and country of the customer. Further, it could be that you need the customer’s country when making a calculation in the order table. This is a field that you don't have in the order table. In SQL you would join the two tables to get country into the order table.

 

In the QlikView script you would instead first define the mapping table that maps a customer ID to a customer country as follows:

 

     MapCustomerIDtoCountry:
     Mapping Load CustomerID, Country From Customers ;

 

The second step is to use this information when reading the order table, such as:

 

     Orders:
     Load *,

          ApplyMap('MapCustomerIDtoCountry', CustomerID, null()) as Country
          From Orders ;

Tables2.png

 

The "null()" as third parameter of the function defines what it should return when the customer ID isn't found in the mapping table.

 

And with this, you have joined the field Country with the order table – without using a join. And you have done it faster and with less chance of errors. Bottom line: Whenever you know that you just want to fetch one single value per record – don’t join. Use Applymap() instead.

 

HIC

 

See more in the Technical Brief on Joins and Lookups.

85 Comments

I'm glad to read this from a more experienced and trustworthy voice!

19,518 Views
Not applicable

The real question could be for a case where you would to have to retreive 5 fields from another table.

In terms of performance what would you prefer ?

A single join or 5 applymap?

Regards,

DA

19,518 Views

If one of the tables is large I would definitely prefer five Applymaps.

Also, there is always a risk with joining: Even if you think you have a one-to-one relationship between the tables so that no records will be duplicated in the join, you often in fact have a one-to-many situation so that the number of records increases. Which leads to incorrect calculations. I have seen this happen many times in real life. This is a risk you do not have with Applymap.

HIC 

19,518 Views
MVP & Luminary
MVP & Luminary

Hi Henric - many thanks for the mention and the link.  Happy to read that my thoughts on the ApplyMap function are shared.

0 Likes
19,518 Views
Not applicable

Hi Henric/Steven - I am new to QV, but find ApplyMap and your posts very helpful. Thank you.

0 Likes
19,518 Views
Specialist III
Specialist III

Thanks. This is perfect for me

I had three issues at work. I had found solution but will change to mapping tables

One is tables with records included twice (the same serial number is sometimes included two or three times)  in it. So I found other ways to only select the first serial. But this was a little complex. Mapping is perfect

And customer groups. I wanted the top 20 customers (made up of around 200* customer numbers) grouped by cust combined name  and the rest as other Customers. Mapping is also perfect as it allows the default option (Other or whatever) if cust num is not found

Another situation. I want to replace if found in a mapping table (replacement cust ref as the cust changed their numbers to a different format so we had the old number and new number  ) but keep if it does not appear in this table

So thanks for this blog post (and the other one). Mapping tables is ideal

0 Likes
19,518 Views