04-04-24 12:41 PM
In the following collection, I need to remove duplicates but keep the latest row.
For example:
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.
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
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?
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.
04-04-24 04:05 PM
Thank you Emerson. I've set that up already but I see this when I run the filter:
04-04-24 04:17 PM
Can you show me the full screenshot with filter?
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
a month ago
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
a month ago
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