How can I check if the values of a column of one table exist in a column of another table?

[FerryCarondelet]

Hi,

I have a question regarding the comparison of two columns in two different tables.

I have a small Table1 with around 5000 rows and I’m pretty sure that many of the values exist in the bigger Table2 with around 30000 rows too. Column1 exists in both tables and serves as the key.

I tried using Table Difference Finder node but unfortunately the rows are not in the same order so that this node didn’t work.

Appreciate any help, thx!

[ArjenEX]

Hi @FerryCarondelet

Welcome to the KNIME Community!

You can use the Joiner node for this.

Assuming a table1:

And table2:

whereby there is an overlap of numbers 3 and 5. Connect both inputs to the Joiner node. Select Column1 as the left and right join columns. Select the tickbox Left unmatched rows. This is called a left antijoin. The ouput will be any numbers that exist in table 1 but not in table 2. Naturally, you can achieve the opposite with a right antijoin.

Hope this helps!

[FerryCarondelet]

Thank you Arjen! Your solution helped me a lot

[ipazin]

Hello there,

for such tasks I usually use Reference Row Filter (there is also Splitter version if you want to preserve non-matching values) node.

Welcome to Community @FerryCarondelet!

Br,
Ivan

[FerryCarondelet]

Hey,

good to know that this node exists! I used it for testing my asumption. Thank you @ipazin!