Identify and extract unique values based on two different columns

Question

I have a data set that captures multiple duplicate entries that pertain to the one deal but have differing settle dates. What I'm looking to do is keep in my output file just the deal ID with the latest settle date as it contains the most accurate value based on latest pricings.

Using the above as an example I'd like to keep entries for Deal IDs 23 and 28 that are highlighted in yellow as they have the latest value.

Would using the Unique tool, or a filter be the best way to go about this? Or if there is an even easier way.

Thanks
Dan

mceleavey · Answer

Hi @DanMurphy17 ,

In addition to @Christina_H 's suggestion, you could also use the sum tool to group by the max for each grouping:

Workflow attached.

Hope this helps,

M.

Grouping_mceleavey.yxmd

Christina_H · Answer

A Unique tool always takes the first instance of the unique value, so if you sort your data in descending date order you could then use a Unique tool to take the latest date for each ID.  Alternatively, assuming your data is already in date order you could use a sample tool to take the last row for each ID.