topic Re: Need to know why to use ApplyMaps over joins in QlikView

Need to know why to use ApplyMaps over joins

deepali_more — Thu, 11 Feb 2016 06:45:47 GMT

Hello All,

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

Re: Need to know why to use ApplyMaps over joins

Chanty4u — Thu, 11 Feb 2016 08:52:03 GMT

hi,

chk dis

Associate, Join or Apply? That is the question.

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

Re: Need to know why to use ApplyMaps over joins

Chanty4u — Thu, 11 Feb 2016 08:53:51 GMT

dis too

Re: joins in qlikview

Re: Need to know why to use ApplyMaps over joins

jagan — Thu, 11 Feb 2016 09:01:13 GMT

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.

Re: Need to know why to use ApplyMaps over joins

deepakqlikview_123 — Thu, 11 Feb 2016 09:06:00 GMT

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

Re: Need to know why to use ApplyMaps over joins

varshavig12 — Thu, 11 Feb 2016 10:21:37 GMT

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.

Re: Need to know why to use ApplyMaps over joins

avinashelite — Thu, 11 Feb 2016 10:49:52 GMT

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?)

Re: Need to know why to use ApplyMaps over joins

Anonymous — Thu, 11 Feb 2016 16:06:11 GMT

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)

Re: Need to know why to use ApplyMaps over joins

deepali_more — Fri, 12 Feb 2016 19:03:19 GMT

A useful one!!! thank you

Re: Need to know why to use ApplyMaps over joins

varshavig12 — Mon, 15 Feb 2016 03:29:38 GMT

You're welcome.

Regards

Varsha

Re: Need to know why to use ApplyMaps over joins

varshavig12 — Mon, 15 Feb 2016 03:39:17 GMT

If you got your answer , you can mark it correct and close the thread.

Regards

Varsha

Re: Need to know why to use ApplyMaps over joins

deepali_more — Mon, 15 Feb 2016 08:05:57 GMT

Thanks Nakul!!

Re: Need to know why to use ApplyMaps over joins

avinashelite — Tue, 16 Feb 2016 05:21:36 GMT

if you have got the answers please mark the helpful and correct answers so that it will be helpful for others

Re: Need to know why to use ApplyMaps over joins

hic — Tue, 16 Feb 2016 10:08:02 GMT

Joins can potentially duplicate records, thus creating a table with incorrect numbers. Applymap() is in almost all cases the better choice: Faster and less error prone.

See more on Don't join - use Applymap instead

HIC