Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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
;