Don't join - use Applymap instead

[hic]

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().

[hic]

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 ;

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.

[rsdhavle]

Thank you for the explanation!!

[w0jt4z]

Nice. 
Maybe someone would help me in my problem. I added table into the mapping, but in the mapped table i have value, which create circular references. I can't rename this field from this table, I would to rename this field after mapping. How can I do that?

[bohsaly_mohamad_24]

Dear Henric,

What do you advise to do regarding joining tables? What is the best practice followed? Loading excel files into QVDs then joining QVDs in a different Script file?

In addition, any data model design tips?

Thanks,

Mohamad

[Kara_himanshu]

What if we don't add the third parameter?

[anderseriksson]

Then like the documentations says you get the second parameter in return.

[Nancy_Jain]

Thanks Henric for this post.
@hic - Please help. Thanks in advance
I have scenario where  I have two tables one from Big Query and another from Teradata. 
Both have Employee Id , FY week and FY year as common rest other columns are different .
Teradata table has columns - Employee ID, FY Week , FY year , SA, OT , AWD , Absence
SA , OT columns will have data always for previous week , where Absence and AWD has data for current week.

BQ aggregate table  has - Employee ID, Employee Name , FY Week , FY Year, Work Adjusted hours , Scan hours and other columns which are not in TD table.

I have applied  outer joined both the tables based on Employee ID, So that I can all the columns from the tables so that in UI I have to show a table where against Employee name SA, OT , Absence and AWD , Work adjusted should come in single row.
Issue - I am not a getting a single row for an employee where SA, OT , Absence, Work adjusted hours should come. I am getting two records . 

should I use Join or use apply map ?

Below is like Teradata table data looks.

Employee ID	FY week	Absence	SA	OT	AWD
104	13	1:10
104	12		2:16	3:00

[hic]

I would try joining on several fields: "Employee ID" , "FY week" and "FY year". Then you should get all on one line.

HIC

[Nancy_Jain]

I have tried that as well but joining two tables on three columns is resulting in missing SA and OT value . Later on I tried joining tables based on only Employee ID , that resulted in two different rows for different weeks