Conditional removal of duplicates

[Banksy]

Hello everyone,

If there are two lines of the same information, you need to use result to judge. Those that pass should be retained, and those that fail should be removed.
But if there’s only one fail record, it’s still there.
Do you have good ideas?
Thanks in advance.

[mlauber71]

@Banksy you could try and use the new duplicate row filter which would allow to specify additional conditions about which entry to use when there are identical entries (eg. sort by the ‘result’ column).

School of duplicates - and how to deal with them

Dealing with duplicates is a constant theme with data scientist. And a lot of things can go wrong. The easiest ways to deal with them is SQL’s GROUP BY or DISTINCT. Just get rid of them and be done. But as this examples might demonstrate this might not always be the best option. Even if your data provider swears your combined IDs are unique especially in Big Data scenarios there might still be lurking some muddy duplicates and you should still be able to deal with them. And you should be able t…

Also you could define a RANK variable for a group (your duplicates you wish to identify) with the help of SQLite or H2 in KNIME and then add the ‘Result’ as an additional sorting option.

SQLITE and Window Functions

@tobias.koetter thank you for this hint this opens up the whole world of window functions. I downloaded the latest JAR file from the Maven site and told KNIME where to find the driver. [image] [image] @khatuaani now you can benefit from the latest developments with H2 (and SQLite).

If you just want to remove the Fails you just could use a Row Filter:

Rule-based Row Filter

[Daniel_Weikert]

@mlauber71 provided the best answer.
Alternatively
If your data is sorted and you group by all columns except Result you could use last I guess

[Banksy]

@mlauber71 Thank you so much for your help!