topic Re: Left Join Issue in QlikView

Left Join Issue

NavinReddy — Sat, 16 Nov 2024 01:07:55 GMT

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

Re: Left Join Issue

sonkumamon — Wed, 04 Mar 2020 17:19:36 GMT

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?

Re: Left Join Issue

NavinReddy — Wed, 04 Mar 2020 17:34:25 GMT

@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

Re: Left Join Issue

NavinReddy — Thu, 05 Mar 2020 07:45:56 GMT

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;

Re: Left Join Issue

sonkumamon — Thu, 05 Mar 2020 07:45:28 GMT

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

Re: Left Join Issue

NavinReddy — Thu, 05 Mar 2020 07:47:38 GMT

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

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

Re: Left Join Issue

tresB — Thu, 05 Mar 2020 07:55:02 GMT

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)

Re: Left Join Issue

NavinReddy — Thu, 05 Mar 2020 08:14:14 GMT

@tresB 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.

Re: Left Join Issue

tresB — Thu, 05 Mar 2020 08:20:09 GMT

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.

Re: Left Join Issue

NavinReddy — Thu, 05 Mar 2020 10:24:25 GMT

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?

Re: Left Join Issue

marcus_sommer — Thu, 05 Mar 2020 12:25:10 GMT

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

Re: Left Join Issue

NavinReddy — Thu, 05 Mar 2020 15:38:46 GMT

@marcus_sommer Thanks you so much for your detailed explanation, its tough to understanding for me, below are the listed fields could you please help how to apply the logic. Kindly help me.

From [..\DataSources\CRE].qvd(qvd);

Re: Left Join Issue

marcus_sommer — Thu, 05 Mar 2020 16:06:47 GMT

You need just to regard your order of fields - and of course you need to specify which of the field(s) is your KEY:

map: mapping load KEY, F1 & '|' & F2 & '|' & F3 ....;
LOAD
[Load_Date] as [F1],
[ID1] as [F2],
[LoB Name] as [F3],
[Sponsor Name] as [F4],
[ID2] as [F5],
[Project Title] as [F6],
[Commitment Date] as [F7],
[Sub] as [F8],
[ Status] as [F9]

From [..\DataSources\CRE].qvd(qvd);

bigTable:
load
*,
subfield(applymap('map', KEY, 'YourDefault1|'), '|', 1) as [As of Date_Exe],
subfield(applymap('map', KEY, '|YourDefault2'), '|', 2) as [ID_Exe1],
subfield(applymap('map', KEY, '||YourDefault3'), '|', 3) as [LoB Name_Exe],
...
from Source;

- Marcus

Re: Left Join Issue

NavinReddy — Fri, 06 Mar 2020 13:07:21 GMT

@marcus_sommer thanks a lot for your reply

i just tried to implement this logic and I got the 'Key' not found error, kindly help me. i am attaching the sample dimension table and Master table. kindly help me

Re: Left Join Issue

marcus_sommer — Fri, 06 Mar 2020 14:36:08 GMT

You need of course to adjust my example to your existing fields. Whereby I think there are no common field(s) within both tables and then the data couldn't be merged - regardless which approach (join or mapping) should be used.

- Marcus