Remove duplicates from collection but keep latest row?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-04-24 12:41 PM
In the following collection, I need to remove duplicates but keep the latest row.
For example:
- Keep item ID 37304873 with the completed date of 25/01/2024 but remove the row containing the same ID that has an exception date of 24/01/2024.
- Keep item ID 21116344 with the exception date of 29/01/2024 but remove the row containing the same ID that has an exception date of 24/01/2024.
I'm installed a BO that will remove the duplicates, but it doesn't always keep the latest row, even after sorting.
Any advice would be very much appreciated.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-04-24 01:21 PM
Hello @david.jeffery, I believe that the better option is, create a collection contains "Item Ids" with unique values and filter the colunms like
"[Item ID] = '" &[Data Item with Item ID]&" ' AND ([Exception] = MAX(Exception]) OR ([Completed] = MAX(Completed]))"
After that you can append the filter result in another final collection.
My collection
My filter
My output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-04-24 02:27 PM
That looks great @EmersonF , if possible could you expand a little further on the exact steps you're taking to achieve this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-04-24 04:01 PM
Of course, my friend.
So, let by step...
1º Remove duplicate values only from "Item ID"
2º With a collection with only unique ID items, run a loop
3º Inside the loop there must be the Filter action containing the filter:
"[Item ID] = '" &[Collection Generated in step 3.Item ID]&" ' AND ([Exception] = MAX(Exception]) OR ([Completed] = MAX(Completed]))" The result of this filter , will result in just one more current line.
4º Still within the loop below the filter, add an action that will insert the filter result into a final collection of your choice.
5º After performing all the steps, you will have a final collection with all the unique and most recent items.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-04-24 04:05 PM
Thank you Emerson. I've set that up already but I see this when I run the filter:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-04-24 04:17 PM
Can you show me the full screenshot with filter?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-04-24 04:51 PM
[Queue Data] is the original collection containing duplicates.
[Unique Item ID's.Item ID] is the collection containing the unique item ID's
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-04-24 11:47 PM
Fixed some issues with the formatting. It kind of works but only if there are duplicate item ID's, if the item id isn't duplicated I'm not seeing any results
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
05-04-24 12:07 AM
Sorry for my delay, in this case, you can make three filters by ID, exceptions and completed and validate the result
1 filter "[Item ID] = '" &[Collection Generated in step 3.Item ID]&" ' "
2 Filter: "[Exception] = MAX(Exception])"
3 Filter: 2 Filter: "[completed] = MAX(completed])"
If has result in filter 2 or 3 you get the result e put in the final collection