cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyabes Frequent Visitor
Frequent Visitor

Remove duplicate and keep row based on a column with the lowest value

I'm tyring to remove duplicate on the ID column but would like to keep the row based on the date with the lowest value. Basically, keep the ID with the earliest instance. I tried the Sort -> Table.Buffer -> Remove Duplicate approach but that process is incredibly slow most likely because the dataset is an append of multiple large CSV files.

 

Are there other approaches that are more efficient?

 

2018-12-09_9-09-24.png

 

Thanks in advance!

David

2 ACCEPTED SOLUTIONS

Accepted Solutions
AlexisOlson Member
Member

Re: Remove duplicate and keep row based on a column with the lowest value

I'd suggest doing a Group By in the query editor. Group by ID and use Min as the aggregation type for the Date column.

Community Support Team
Community Support Team

Re: Remove duplicate and keep row based on a column with the lowest value

Hi @dyabes

 

Attached the sample file for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
AlexisOlson Member
Member

Re: Remove duplicate and keep row based on a column with the lowest value

I'd suggest doing a Group By in the query editor. Group by ID and use Min as the aggregation type for the Date column.

dyabes Frequent Visitor
Frequent Visitor

Re: Remove duplicate and keep row based on a column with the lowest value

Thank you. I think I should have provided the complete dataset I'm wokring on. I also need to keep the corresponding row values from other columns

 

2018-12-09_10-36-51.png

Highlighted
AlexisOlson Member
Member

Re: Remove duplicate and keep row based on a column with the lowest value

You can do what I suggested an then merge the extra column(s) back in after.

Community Support Team
Community Support Team

Re: Remove duplicate and keep row based on a column with the lowest value

Hi @dyabes

 

Attached the sample file for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
expertomer Frequent Visitor
Frequent Visitor

Re: Remove duplicate and keep row based on a column with the lowest value

Hi Dear

You need to use Group By.

Select Group By -> Select ID column as Group BY  and then in Operation select Min and in Column Select Date.
You will get your result.