Compare data in 2 columns

Hi there,

I am pretty new to KNIME and I have a small subprocess as part of a larger process which needs to check that a string in one column is present in another column, although it may not be on the same row.

For instance I have a list of employee numbers for the whole building, and I need to check that the ones moving through the wider process match a number on the larger list. I have appended the 2 spreadsheets/columns together but need to compare the 2 columns and create an output table of payroll numbers not recognised. I have tried column comparator but this only seems to compare row to row whereas I need to check against the whole list.

image

Thanks

Hi Jenny-King, and welcome! This can be solved with the JOINER node. First, you will need not append the two sheets together… Joiner will take care of that. Set your Join mode to Left Outer Join, and on the “Column Selection” tab, uncheck the box for “Remove joining columns from bottom input (‘right’ table)”. After your join completes, matched pairs will show, and unmatched pairs will have a “?” in the output. Hope this helps!

2 Likes

Hi Snowy,

In this case I do not want it to remove the unmatched ones, I want a report that highlights which numbers on the weekly report do not appear on the main list, so that these exceptions are highlighted. I added in a case that I knew would not match on the test sheets but in the join it has now been removed.

Is there a way to swap it round to show only the unmatched string from column?

Look also at


node.

3 Likes

Thank you so much for this - has given me exactly what I need.

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.