Substract one table from another

Hello everyone,

I have a new problem which I'm able to solve but in a very complicated way so I want to know if there is an easier way to handle this. What I want to do is to substract one table from another. That means for example I have a table with all values and another one only with the unique values of the first table. Now I want to have a table only with the duplicates that means a substraction of the uniques out of the full table.

In SQL it would be a Left Join with a further restricition but in the KNIME "joiner"-node I cannot make any specifikations. Attached is a image which shows the join I actually need.

So is there any node a process to solve this problem in a simple way?

Thanks in advance!


What are you actually trying to get to - is it a table showing which values are found more than once in the original table and where? If so you can get this using GroupBy:

  • Use RowID to append a column to the original table containing the row IDs
  • Use GroupBy to group on all the columns from the original table and aggregate on the Count of the added row ID column
  • Use Row Filter to keep only the rows where the count is 2 or greater
  • Use Joiner to do a left outer join between this table (top input) and the table from RowID (bottom input), matching all columns from the original table.

You now have a table showing the duplicate rows and their row ID from the original table.

Thanks for your answer. You're completely right. But the case I've given with the duplicates was just an example. But how to this if I don't look for duplicates but just have two random tables and I want to have this kind of join (join the datas in this way that I get as result only the datas that are in Set A but not in Set B if they have common values).