Left Join with Where clause on the Join

[Anonymous]

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.

[felipedl]

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;

[QlikNoviceNo1]

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;

[felipedl]

Awsome,

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

Thanks.