topic Re: Join only where doesn't exist in QlikView

Join only where doesn't exist

JustinDallas — Thu, 19 Feb 2015 04:14:33 GMT

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.

Re: Join only where doesn't exist

PrashantSangle — Thu, 19 Feb 2015 04:28:45 GMT

Use Left Join and not exist()

try like

Load id,* from table1;

Left Join

Load id,* from table2

where not exist (id,id)

Regards

Re: Join only where doesn't exist

rwunderlich — Thu, 19 Feb 2015 05:40:43 GMT

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

Re: Join only where doesn't exist

JustinDallas — Thu, 19 Feb 2015 17:15:59 GMT

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.

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

;