Replace/Simplify row values to eliminate duplicates

[TigerCole]

Hi…

I have a table that has duplicate rows that I need to sort out. I cannot use the duplicate filter because there is another field that is linked to it so this is part of a much bigger KNIME flow. Below is an image of what I need to fix…

What I need to do is make sure that both rows have the same [ContactID] and [AddressID]. It does not matter which, but I have to make sure that both have the same ID fields. I will have to apply the same method in other parts of the flow as well to clean up other fields

Is there a simple and reliable way to do this?

Thanks.

tC/.

[mlauber71]

This thread and the discussion might be relevant for you:

Delete duplicates

You could use a SQL function with row numbers and only keep two. The mentioned example is from a big data workflow. But it seems H2 is also allowing to use row_number() function *1). *1) http://www.h2database.com/html/changelog.html

[HansS]

Hi @TigerCole

With the RowID node you can Create new column with the RowID values, and use this new column in the Duplicate Row Filter

gr. Hans

[TigerCole]

Hi @HansS and @mlauber71

Thanks for your responses. I have tried using RowId node suggestion but I should have included 2 extra factors that I have to take into account.

1. I have to use one of the [ContactID] & [AddressID] pairs because they are foreign keys in other tables. I can use either, but I have to use 1 of them, I cannot create a new one.
2. I cannot use the duplicate filter because there is a list field in another column that I need ungroup so I cannot lose any rows.

In this example, the ungroup will result in the same 2 rows but in others, the 2 rows maybe ungroup into 10, but the [ContactID] & [AddressID] must all be the same because ungrouped rows are going to be regrouped again using the ‘repaired/fixed’ [ContactID] & [AddressID] pair.

Can you suggest another elegant/simple solution? This set is 2 million rows so elegant/simple is good.

Thanks

tC/.

[mlauber71]

I see these points:

• in deduplication you can aggregate towards a key. That means you will loose any other key that is not been aggregated to - if you want to preserve the information you will have to do something like
  ** aggregation functions like min, max, avg
  ** llists (list all the values in one cell) the group by function has such things
  ** separate variables that contain the information like
  *** preserved_id_1, preserved_id_2
• then you can choose to keep one line based on several criteria (that would contain the original structure and row integrity - like discussed in the links below). You would loose the information from the other lines
  ** if you want to preserve them you will have to aggregate them separately and then re-join via a preserved ID (preferably your key value)

The reason why I (somtimes) like the solution with the row_number forcing the reduction to a single unique line is that especially in very large datasets with a cascaded group by (over several fields) some duplicates might be lurking even if the providing database sweared that would not be possible (very common in Big Data environments). So if you want to be sure just eliminate a lone duplicate.

But that very much depends on your business case. If you cannot loose any information at all you will have to resort to one of the methods mentioned above.

Remove rows with duplicate values

I set up a workflow to demonstrate how this could be done use group by to calculate how many duplicates there are (note: KNIME should introduce a generic COUNT(*) function - I had to use a variable) if the count is larger then 1 it is a duplicate left join it back to the original data sort the data by ID and other variables if you want to keep one of the duplicates use the LAG column to identify which line is a 2nd, 3rd occurrence of a duplicate make a rule to keep just a single line of each …

Remove rows with duplicate values

I would like to go into my reasoning for deliberately focussing on an ID. Yes you can just remove duplicates by Group by or SELECT DISTINCT. In a lot of cases you have something like customer IDs or phone numbers. With DISTINCT what would happen in such a case phone_no | adress | joined 555-1234 | main street 1 | 2008 555-1234 | main street 1C | 2009 555-1234 | Main Street 1 | 2008 you would still end up with 3 distinct cases and you would call/mail the customer three times, and what if yo…

[TigerCole]

Hi @mlauber71 …

I am a fan of your 1st solution in your reply. I have implemented it in a flow that runs every night to process between 5 and 6 million transaction rows to update a customer segmentation report. I have been tempted to replace it with a Duplication Filter node but feel much more comfortable with the process as it is. It does take a few seconds longer but I like the additional insight of your workflow. I agree with your comment on the need for a COUNT(*) function.

As you suggested as well … I will probably have to implement an aggregation (min/max) method and then “rebuild” the key values.

Thanks for your valuable responses.

tC/.

[mlauber71]

I have put together a sample workflow and article how to deal with duplicates in KNIME. Maybe you can derive some insights from there

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…

School of duplicates - and how to deal with them – mlauber71

[webstar]

Really Nice - Interestingly a null last contact for ID B_002 is carried thru in the example with the DB reader due to the sorting. Obviously there are lots of ways to get that date back. As an example i added a group by ID with max last contact attached to [result of row_id Magic] and prior to the Rule based Row filter and then a joiner node and then would some rule node to re-append. Whats the easy way to get something like that back?

[mlauber71]

@webstar

a null last contact for ID B_002

Yes this is due to using the whole line according to the entry_date. One could just use the Duplicate Row Filter and sort by last_contact descending and rejoin like with the largest purchase ever.

Or you could just do that within SQL (Hive) and select a FIRST or LAST VALUE like this:

so every line would get this value and you would have it if you filter to your chosen row: