topic Re: The opposite of join in QlikView

The opposite of join

Fri, 05 Dec 2014 21:21:50 GMT

Hey all.

I have 2 tables, which one of them is actually contained within the other. I need to cut out that part of the other table, like in the following example:

Table 1:	Table 2:	The resulting table should be: Table 3:
ID	ID	ID
1	1	2
2	3	5
3	4	6
4
5
6

How do I do that?

Thanks a lot for your help!

Merav.

Re: The opposite of join

JonnyPoole — Fri, 05 Dec 2014 21:27:32 GMT

This will pull all the IDs from Table1 that are not in Table2...

qualify *;

Table1:

load * inline [

];

Table2:

load * inline [

];

UNQUALIFY *;

Result:

load

Table1.ID as ID

Resident Table1

where not ( exists(Table2.ID,Table1.ID));

Re: The opposite of join

its_anandrjs — Fri, 05 Dec 2014 21:33:44 GMT

Hi,

In place of the Joins use Where Not Exists functionality

Ex:-

Table2:

LOAD * Inline [

ID1

4 ];

Table1: //The resulting table should be:

LOAD * Inline [

ID2

6 ]Where not Exists(ID1,ID2);

DROP Table Table2

Regards

Anand

Re: The opposite of join

MarcoWedel — Fri, 05 Dec 2014 23:55:46 GMT

tabTemp:

LOAD ID as ID2

Resident Table2;

Table3:

LOAD *

Resident Table1

Where not Exists(ID2, ID);

Drop Table tabTemp;

Re: The opposite of join

Sat, 06 Dec 2014 00:20:34 GMT

You are right. All of you. Thanks a lot for the help.

I just had to figure out that it cannot be done with all the fields in the table. I used the not exists, as you explained, and then added the rest of the fields using "left join".

Thanks so much for the great help!

Re: Re: The opposite of join

MarcoWedel — Sat, 06 Dec 2014 10:46:59 GMT

Table1:
LOAD @1 as ID,
    'Table1' as table
FROM [http://community.qlik.com/thread/144882] (html, codepage is 1252, no labels, table is @1, filters(Remove(Row, Pos(Top, 1)),Remove(Row, Pos(Top, 1))))
Where Len(@1);

NoConcatenate

Table2:
LOAD @2 as ID,
    'Table2' as table
FROM [http://community.qlik.com/thread/144882] (html, codepage is 1252, no labels, table is @1, filters(Remove(Row, Pos(Top, 1)),Remove(Row, Pos(Top, 1))))
Where Len(@2);

tabTemp:
LOAD ID as ID2
Resident Table2;

NoConcatenate

Table3:
LOAD ID,
    'Table3' as table
Resident Table1
Where not Exists(ID2, ID);

Drop Table tabTemp;

hope this helps

regards

Marco

Re: The opposite of join

Sun, 07 Dec 2014 10:03:11 GMT

Thank you very much, Marco!

Re: The opposite of join

MarcoWedel — Sun, 07 Dec 2014 10:47:28 GMT

You're welcome.

Regards

Marco