topic Re: Left Join with Where clause on the Join in QlikView

Left Join with Where clause on the Join

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have not found an answer to how to write a Left Join where I only want the rows in the Left table that DON'T have a value in the right table. Maybe this is a 2 step process of creating the joined table and then reloading with the Where clause? Thanks for your help.

Re: Left Join with Where clause on the Join

felipedl — Fri, 05 Oct 2018 13:16:35 GMT

Just write of the regular left join and on a second resident load, do a check to which values don't exist.

Something like this:

data:

Load * inline

[

Id,Value

1,A

2,B

3,C

];

NoConcatenate

joinData:

Load * inline

[

Id,Value

1,A

2,B

];

left join (data)

Load *,

1 as [Flag Exists]

Resident joinData;

NoConcatenate

finalData:

Load *

Resident data

where isNull([Flag Exists]);

drop tables data,joinData;

Re: Left Join with Where clause on the Join

QlikNoviceNo1 — Fri, 05 Oct 2018 14:49:31 GMT

Thanks Felip for the solution.

I tried the below and it worked. I renamed the common field in table2 to make this happen.

data:

Load * inline

[

Id,Value

1,A

2,B

3,C

];

left join (data)

Load * inline

[

Id,ValueB

1,A

2,B

] ;

NoConcatenate

Final:

Load *

Resident data

where IsNull(ValueB);

drop Table data;

Re: Left Join with Where clause on the Join

felipedl — Fri, 05 Oct 2018 16:14:51 GMT

Awsome,

Can you mark the correct anwser as correct so others can see it?

Thanks.