How do I do an anti join?

Question
1

How do I do an anti join? I want to find all the rows in a dataset whose key values do not appear in another dataset.

For example:

If A contains rows with key values:
A,B,D,E,F

And B contains rows with key values:
A,C,E,F,G

A anti-join B gives rows with key values:
B,D

B anti-join A gives rows with key values:
C,G

2

You can do an anti join using the Subtract transform.

Make sure the original dataset is above the other dataset in the center pane. So for A subtract B:

image
image691×366 51.4 KB

anti-join.transform (2.1 KB)

1 Like
3

A question for @Admin , is it possible to “see” what has been subtracted ? I can see X rows have been removed , but cannot figure how to see the content of which ones

4

Yes, just use a second Subtract:

image

1 Like
5

Oh my ! IT works , Thank You