Substract one table from another

KNIME Analytics Platform
1

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!

Marius

2

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.

3

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).