topic Re: How to verify if 'Left join...where exists' worked correctly in QlikView

How to verify if 'Left join...where exists' worked correctly

QFanatic — Wed, 14 Apr 2021 10:13:14 GMT

hi guys

My script looks like this..

Temp_Events:
LOAD #Key_Events,
Event_Id,
Event_Tp_Cd,
IsRequest,
IsEvent,
Event_Type,
Unq_No_In_Orig_Src_Sys,
Orig_Src_Sys_Cd,
Source_Name,
Event_Strt_Date,
Event_Strt_Tm,
Event_Strt_Dt_Tm,
Event_End_Date,
Event_End_Tm,
Event_End_Dt_Tm,
SubtractMillisecondFromTS3,
Sys_End_Tms,
Sys_Strt_Tms
FROM
$(vQvData)Temp_Events.qvd(qvd);

left join (Temp_Events)

load
AutoNumber(Event_Id & '_' & Orig_Src_Sys_Cd) as #Key_Events,
Rltd_Event_Id ,
1 as IsResponse
where exists (#Key_Events, AutoNumber(Event_Id & '_' & Orig_Src_Sys_Cd))
;
select
Rltd_Event_Id ,
Event_Id ,
Event_Event_Rltnp_Tp_Cd,
Rlt.Orig_Src_Sys_Cd
FROM
$(v)."Event_Event_Rltnp" RlT
left join $(vLkp).Lkp_System SYS
ON RlT.Orig_Src_Sys_Cd = SYS.Orig_Src_Sys_Cd
AND SYS.orig_src_sys_cd = SYS.system_cd
;

What is the quickest way to see if the join worked correctly? I guess one would need to see if some field is null or not by doing a resident load, but my tables are HUGE, ideally would not want to do that.

Thanks

Re: How to verify if 'Left join...where exists' worked correctly

martinpohl — Wed, 14 Apr 2021 10:50:33 GMT

the combination left join #key where exists #key is not nesseary.

A left join only would join datas where the #key field in the join table exists.

Otherwise you would use an outer join that there are more datas than before.

Or use an inner join only to have only datas from noth tables from.

To see which datas have only datas in the first table, create a table, use #key, a field from first table (eg event_id) and a field from second table (rltd_event_id).

the sort by rlt_event_id, datasets woth no values should stay at the end.

Regads.

Re: How to verify if 'Left join...where exists' worked correctly

QFanatic — Wed, 14 Apr 2021 11:34:28 GMT

hi Martin,

Thanks for your reply.

Re: How to verify if 'Left join...where exists' worked correctly

QFanatic — Wed, 14 Apr 2021 11:50:29 GMT

Martin,

I now remember why I used the "where exists". The table that I join to is HUUUUUUUGE (billions of rows) and I was hoping by using the Where Exists, I would limit the records returned and the reload time as well.

Does that make sense?

Thanks

Re: How to verify if 'Left join...where exists' worked correctly

martinpohl — Wed, 14 Apr 2021 11:56:37 GMT

because the sql statement don't know anything from the exists statement, in the first step of the load all datas are selected on database. Then only exists values are transfered to the load script. I dont't know if there is a time or performance effect.

Regard