The opposite of join

[#1]

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.

[JonnyPoole]

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

qualify *;

Table1:

load * inline [

  ID

  1

  2

  3

  4

  5

  6

];

Table2:

load * inline [

  ID

  1

  3

  4

];

UNQUALIFY *;

Result:

load

  Table1.ID as ID

Resident Table1

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

[its_anandrjs · Accepted solution]

Hi,

In place of the Joins use Where Not Exists functionality

Ex:-

Table2:

LOAD  * Inline [

ID1

1

3

4  ];

Table1:  //The resulting table should be:

LOAD * Inline [

ID2   

1   

2       

3       

4       

5       

6        ]Where not Exists(ID1,ID2);

DROP Table Table2

Regards

Anand

[MarcoWedel]

tabTemp:

LOAD ID as ID2

Resident Table2;

Table3:

LOAD *

Resident Table1

Where not Exists(ID2, ID);

Drop Table tabTemp;

[#5]

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!  

[MarcoWedel]

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

[#7]

Thank you very much, Marco!

[MarcoWedel]

You're welcome.

Regards

Marco