Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Left Join Issue

Hello All,

I was trying to join 5 fields using left join to the Master Table. when i was using left join its taking more than 1 hour for QVW reload.  Without left join its just taking only 10Minutes time to reload. 

help me is there any another way can i join 5 fields to Master Table. 

Thanks In Advance

NR

Labels (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Left Join Issue

It depends on various parameters which loading-type - resident or per file (qvd) - is more suitable. If the RAM is short and the data are swapped to the virtual RAM it's usually better to store the origin table into a qvd and drop it then and the following steps load then new from the qvd. If your network/storage performance is rather poor you may intentionally use the reverse approach. Also the capabilties of the CPU to process all the loading/transforming will have an impact. This means you need to measure which part is your biggest bottleneck to bypass it as much as possible.

Beside this by your mentioned amount of data you should consider to apply a multi-stage data-architecture and/or to implement an incremental load-logic.

Personally I use very seldom joins especially not if the datasets are rather big and/or there are risks to double/reduce the number of records in regard to the table-relation of the keys respectively to the data-quality. The much better approach is the already mentioned mapping - it's simple and fast, you could define the values  of non-matching keys and with concatenated values you could also use a single mapping for multiple fields. Here an example:

map: load KEY, F1 & '|' & F2 & '|' & F3 inline [
KEY, F1, F2, F3
1, a, b, c
....
];

bigTable:
load
   *,
   subfield(applymap('map', KEY, 'YourDefault1|'), '|', 1) as F1
   subfield(applymap('map', KEY, '|YourDefault2'), '|', 2) as F2
   subfield(applymap('map', KEY, '||YourDefault'), '|', 3) as F3
from Source;

- Marcus

View solution in original post

14 Replies
Highlighted
Contributor III
Contributor III

Re: Left Join Issue

You need to provide us with more information:

1. How many rows are in the master table?

2. On what keys do you join the tables?

Can you provide us with the script?

Highlighted
Creator II
Creator II

Re: Left Join Issue

@sonkumamon In master table i have more than one billion of rows, its new dimentions table have 5 new fields. Here i am trying to join the 5 fields to the Master table. Thanks  

Highlighted
Creator II
Creator II

Re: Left Join Issue

Any suggestions are appreciated. Below is my new dimension table, want to join to Master Table.  please help me to resolve this issue. thanks

Left Join (CRE)
LOAD
[Load_Date] as [As of Date_Exe],
[ID1] as [ID_Exe1],
[LoB Name] as [LoB Name_Exe],
[Sponsor Name] as [Sponsor Name_Exe],
[ID2] as [ ID_Exe2],
[Project Title] as [Project Title_Exe],
[Commitment Date] as [Commitment Date_Exe],
[Sub] as [Sub_Exe],
[ Status] as [ Status_Exe]

STORE CRE into [..\DataSources\CRE].qvd(qvd);
END IF;

Highlighted
Contributor III
Contributor III

Re: Left Join Issue

You can try using ApplyMap. The performance are better then left join.

Highlighted
Creator II
Creator II

Re: Left Join Issue

i want just trying to joining the 9 fields to the Master table will it work?

Left Join (CRE)
LOAD
[Load_Date] as [As of Date_Exe],
[ID1] as [ID_Exe1],
[LoB Name] as [LoB Name_Exe],
[Sponsor Name] as [Sponsor Name_Exe],
[ID2] as [ ID_Exe2],
[Project Title] as [Project Title_Exe],
[Commitment Date] as [Commitment Date_Exe],
[Sub] as [Sub_Exe],
[ Status] as [ Status_Exe]

STORE CRE into [..\DataSources\CRE].qvd(qvd);
END IF;

Highlighted
MVP
MVP

Re: Left Join Issue

Joins are usually resource consuming. Joining with billion records is going to take time. And also, it's very unusual that a master table has billion records; generally this happens with transaction tables. 

 - check if your key field has duplicate values in master table

 - check if your join is happening on multiple fields (creating synthetic keys)

 

Highlighted
Creator II
Creator II

Re: Left Join Issue

@tresesco thanks for reply

All distention tables has unique names, i have checked the table viewer do not have any synthetic keys. Is there any other method can i use to join these fields to Master table. Thanks.

Highlighted
MVP
MVP

Re: Left Join Issue

ApplyMap() is generally faster than joins, however, since you have five fields to bring - it would be five applymap(s), not sure if that would improve performance - but worth giving a try.

Highlighted
Creator II
Creator II

Re: Left Join Issue

i will limit to use only 5 fields from the dimention. i never tried Apply Map() function, Can you help me how to implement. Thanks.
Is resident load will increase the reload performance?