Need to know why to use ApplyMaps over joins

[deepali_more]

Hello All,

I am bit confused in the use of applymaps and joins. what  to prefer over and Why??

[Chanty4u]

hi,

chk dis

Associate, Join or Apply? That is the question.

https://community.qlik.com/docs/DOC-8876

[Chanty4u]

dis too

Re: joins in qlikview

[jagan]

Hi,

If you join you cannot assign a default value to a null value if you join, if you do applymap then you can set the default value.

Table1:

EmpID, DeptID

1, Dept1

2, Dept2

3, Dept3

Table2:

DeptID, DeptName

Dept1, HR

Dept2, Sales

If you join the above two tables the DeptName for EmpID 3 is null, if you use applymap() then you can set default value as N/A like below

LOAd

*,

Applymap('DeptMapping', DeptID, 'N/A') AS DeptName

FROM Table1;

Also Applymap() is faster when compare to join, you can see this difference when you have huge data.

[deepakqlikview_123]

Applymap works as a look up reference table.Takes the value from 1 table and we can put it in other table.it should contain 2 fields.

Thanks

[varshavig12 · Accepted solution]

In ApplyMap

We have two table

Table1: Mapping Table

Table2: In which we use ApplyMap function

QV Implicitly drop table1 and gives the desired output.

Hence, reduces the size of the data.

Also, it is comparatively faster.

Qualitative approach--- default value can be given instead of null. (optional)

Limitation: Mapping table i.e Table1 can have only two fields.

Joins:

We have various types of Join. (left,right,inner,outer)

As per the requirements, we use it.

Table 1 can have more than two fields.

Note: Based on requirement we use Joins or ApplyMap() as they are use for different purposes.

You can read and compare

• LookUp() and ApplyMap()
• Joins,Keep,concatenate.

[avinashelite]

As per me it all depends on your data ,data model and your requirements if you have small data set than join and applymap doesn't have any difference ....if you want to add one column to the table with large data set than Applymap is better than Join ..

It all comes down to how much data you want to combine. A JOIN is very flexible / powerful but also a resource hog. Mapping tables are extremely fast but you won't experience any improvement on small data sets.

Most long-time QlikView developers tend to prefer applymap to any JOIN simply because of its efficiency. When a document has to be reloaded in a densely populated QDS environment, every second and every MB saved is crucial for achieving optimal performance.

To summarize:

• Avoid JOINs, especially on large data sets. You can knock out your server with an audacious JOIN, often leading to the dreaded "General Script Error".
• Use Applymap() for conversions and translations of single fields, or multiple Mapping tables for multiple fields in large data sets (currencies?)

[Anonymous]

All the responses here do a great job in explaining when to use ApplyMap vs Join and vice-versa. To sum up and remember:

1) Mapping load (a per-requesite for ApplyMap) can only be performed on a table with 2 columns

2) Mapping load table is dropped automatically at run-time (so you will not see that table in your data model)

3) ApplyMap helps in reducing no. of leaf tables (tables with 2 rows) in the data model wherever possible (hence reducing the snowflaking)

[deepali_more]

A useful one!!! thank you

[varshavig12]

You're welcome.

Regards

Varsha