I am trying to remove duplicate rows based on the transaction id & charge amount ( IE, while it’s possible to have the same transaction ID, the charge amount will be different. ) Are there any suggested nodes?

Hi @CarishmaM and welcome to the forum. You can try the Duplicate Row Filter node for this. :slight_smile:

Hi Scott - I tried this option, but it doesn’t seem to help much because I using two tables. I was using the Reference Row Filter, but it only allows for the one criteria versus both.

Maybe you need to concatenate or join your data first? It’s hard to say without more details. Can you post some example/dummy input data, along with what you would expect the output to look like?

[image] I have two data sets similar to the image above. The first data set only has data from February 1-14. The second data set has data from February 1-28. I need to identify which rows in the first set have been duplicated into the second set and remove it.

@CarishmaM you could maybe benefit from these discussions: [image] Efficient way of doing a JOIN ... WHERE ... KNIME Analytics Platform Well, not a particularly sophisticated idea but you could try and use a local H2 database to join two datasets by time ranges. Depending on the power of your machine and other factors that might work. I tried it with files of different sizes and at least it gives you a result. [image] Until there is a solution in KNIME you could explore further the possibilities of using SQL to make the join more efficient. I took from this older entry: There might be some special limitations when workin…

In that case I would use a Concatenate first to stack your two tables together, followed by a Duplicate Row Filter. There may be more efficient ways to do it, but if I understand you correctly, that should work.

You can add to each table an identifier column, e.g., constant int value → concatenate - > duplicate row filter → row splitter and tou have your table back. Check the proper options in the filter to ensure that e.g. rows are removed and the order of the rows is being maintained

Okay, so I was able to get this method to work. What if I want to remove all the duplicate. IE if line 1&2 is the same, and I want them both gone?

I suppose in that case you could do a count aggregation with a GroupBy node, then afterward filter out results with counts > 1?

Is there a way to use the GroupBy node without changing how the data is formatted as the output?

How to remove duplicate rows?

KNIME Analytics Platform

mlauber71 May 11, 2021, 1:49pm 6

@CarishmaM you could maybe benefit from these discussions: