Join only where doesn't exist

[JustinDallas]

Hello Everyone,

So I have a table (A) where I want to concatenate it to another table (B). The only issue is that I want to concatenate the values where they don't exist in Table B.  In sql we would do something like

SELECT * FROM Table A LEFT JOIN Table B ON a.id = b.id WHERE b.id IS NULL.

Most of the Qlikview information only concerns where you have joining values, I only want what exists in A, but not in B.

Any help is greatly appreciated.

[PrashantSangle]

Hi

Use Left Join and not exist()

try like

Load id,* from table1;

Left Join

Load id,* from table2

where not exist (id,id)

Regards

[rwunderlich · Accepted solution]

I'm confused as to what the outcome should be. In both the SQL example and the suggested QV solution, it appears to me that nothing would be joined.

Also, you say you want to "concatenate it to another table (B)". Concatenate adds rows (Union is SQL) but then you mention Join which adds fields. Can you clarify what you are wanting and perhaps give a small example?

Do you perhaps want an OUTER Join rather than a LEFT Join?

-Rob

[JustinDallas]

Hello Rob,

    Here is a small script that does what I want after doing what you suggested with the OUTER Join.  It's not quite perfect, and I'll post that question somewhere else, but this is what I was looking for even if I couldn't explain it coherently.

a:

LOAD * Inline [

Username, Code, Blah, Cookies,

    1, a , 1 , 4

    2, b , 2 , 4

    3, c, 3 , 3

    4, d, 4 , 2

    5, e, 5 , 63

];

NoConcatenate

TableWithMissingDataAdded:

LOAD * Resident a

;

OUTER Join

LOAD * INLINE [

    Username, Code, Blah

    3, c, 3

    4, d, 4

    5, Seattle, Seahawks

    10, j, 10

    11, k, 11

]

where not exists(Username,Blah)

;

DROP TABLE a

;