Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

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.

88 Comments
dvasseur
Partner - Creator III
Partner - Creator III

Henric,

I mean, they cannot be dropped during script execution of course!

0 Likes
759 Views
hic
Former Employee
Former Employee

You don't need to drop them. If you use a mapping table in a loop, like Steve Dark does above, you can have dynamic labels e.g. Map$(i) that you can use to reference the right mapping table.

HIC

0 Likes
759 Views
Jason_Michaelides
Luminary Alumni
Luminary Alumni

HIC,

It would be nice to determine when to drop mapping tables though.  We are taught to drop resident tables as soon as they are no longer needed so as to conserve RAM during script execution. If you know you no longer need a mapping table it would be good to be able to drop it before the end.

0 Likes
759 Views
m2r4miller
Contributor III
Contributor III

That is an excellent idea! Would you mind sharing a more complete example?

Thanks!

0 Likes
759 Views
Not applicable

So the first column is always the lookup column?

MapCustomerIDtoCountry:
     Mapping Load CustomerID, Country From Customers ;

   Orders:
     Load *,

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

You look up the customerid, if it match you throw back the country - and if customerid is not in the mapping table, you throw back null? right?

0 Likes
759 Views
hic
Former Employee
Former Employee

You've got it. /HIC

759 Views
MK_QSL
MVP
MVP

Informative

0 Likes
759 Views
Not applicable

Thanks Henric..I am learning a lot very quickly from your posts..

0 Likes
759 Views
rsdhavle
Creator II
Creator II

Very good blog and very helpful..Thanks a lot

0 Likes
780 Views
Not applicable

Hi

What if I have a one to many situation where the ID is unique from the mapping table, but not at the source table that I apply to? So far I still get the correct caculations.

0 Likes
780 Views