topic Re: Remove duplicates from collection but keep latest row? in Product Forum

Remove duplicates from collection but keep latest row?

david.jeffery — Thu, 04 Apr 2024 11:41:15 GMT

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.

Re: Remove duplicates from collection but keep latest row?

EmersonF — Thu, 04 Apr 2024 12:21:51 GMT

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

Re: Remove duplicates from collection but keep latest row?

david.jeffery — Thu, 04 Apr 2024 13:27:28 GMT

That looks great @EmersonF , if possible could you expand a little further on the exact steps you're taking to achieve this?

Re: Remove duplicates from collection but keep latest row?

EmersonF — Thu, 04 Apr 2024 15:01:35 GMT

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.

Re: Remove duplicates from collection but keep latest row?

david.jeffery — Thu, 04 Apr 2024 15:05:59 GMT

Thank you Emerson. I've set that up already but I see this when I run the filter:

Re: Remove duplicates from collection but keep latest row?

EmersonF — Thu, 04 Apr 2024 15:17:45 GMT

Can you show me the full screenshot with filter?

Re: Remove duplicates from collection but keep latest row?

david.jeffery — Thu, 04 Apr 2024 15:51:38 GMT

[Queue Data] is the original collection containing duplicates.

[Unique Item ID's.Item ID] is the collection containing the unique item ID's

Re: Remove duplicates from collection but keep latest row?

david.jeffery — Thu, 04 Apr 2024 22:47:00 GMT

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

Re: Remove duplicates from collection but keep latest row?

EmersonF — Thu, 04 Apr 2024 23:07:45 GMT

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