

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Need to know why to use ApplyMaps over joins
Hello All,
I am bit confused in the use of applymaps and joins. what to prefer over and Why??
- Tags:
- new_to_qlikview
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
dis too

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A useful one!!! thank you


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You're welcome.
Regards
Varsha

- « Previous Replies
-
- 1
- 2
- Next Replies »