anti-join between 2 tables

Hi,

I need to do an anti-join between 2 tables, but the "joiner" node doesn't have this option. Is there any other node that can be used for that purpose? Any ideas about how the anti-join can be done in Knime?

Thank you!

Hi,

what is a Anti-Join?

Sounds interesting. Maybe a Column Splitting? or the reference row filter?

An anti-join between two tables returns one copy of each row from the first table for which no matches are found in the second table. 

I attached a picture from wikipedia.

What you are looking for is the Reference Row Filter node. Please see the screenshot attached to this post.

Best,
Marc

Yes, but that's still a workaround, for you require two Reference Row Filters for it to work the same way: one for the first table using the 2nd as reference and another for the 2nd table using the 1st as reference.

In that case I think you need the Joiner node with "Full Outer Join"- rows not in the left table will have missing values for the left table columns in the output, and rows not in the right table will have missing values in the right table columns in the output.  A couple or row filters, and then you will probably need to do something like use a table splitter to split the left and right columns, a column rename (regex) to remove the suffixes from the right table and a concatenate to put the back together as a single table

Steve

@Geo: We will publish a Reference Row Splitter with 3.1. :-)

Might be 'Set Perator' it's what you're looking for. it 'll give you a table which can be the ref table for 2 successive ref table filters on each table...?

@Geo:

The use of the Reference Row Filter node is the perfect answer to mkfam9's question. So, in case we learned something new today, because mkfam9 asked the wrong question, I feel very sorry for that. And by the way, using two or three nodes for an obviously not so common task should be acceptable.

Thank you everyone for your contribution. The solution posted by "ImNotGoodSry" worked for me and it is what I was looking for.

@ImNotGoodSry
I don't understand why my posting was qualified as "wrong question", but I am open to constructive feedback so that I can improve my future posts.

@mkfam9:

Don't worry, the "wrong question" remark was addressed to "Geo". ;-)

@ImNotGoodSry:

I do perfectly understand the solution involving the reference row filters and there are even other solutions not mentioned here. In data management there are always many good answers for any given problem.

And yes, why not even have the "anti-join" in the Joiner node ? Seems to me like a reasonable feature request. That's why I've called the reference row filter solution a work-around.

Dear Geo,

mkfam9's problem could be solved by using one single node. There was absolutely no need to use multiple Reference Row Filters. So the term "workaround" seems not very appropriate for the given problem.

However, if the use of the term "anti-join" was inappropriate, because an "anti-join" (technically speaking!) is something else, I would say, he asked the wrong question.

I hope you understand what I mean.

Best,
Marc

Ok, I understand what you mean. So the problem asked here appears to be based on the assumption that one can identify each row using a single column in any given set, isn't it ? How would one then tackle multiple column comparisons using Reference Row Filter ? Or would such a circumstance be considered outside of the anti-join's scope ?

> How would one then tackle multiple column comparisons using Reference Row Filter ?

Concentante the columns together in an unambigous and defined format. Use that column in reference row filter.