Identifying duplicate record id's to override a timestamp

I'm trying to identify duplicate values in my dataset in order to pull out the most recent date in a separate field. My goal is to keep the entry with the earliest date stamp so that I have a final file of unique records.

In excel I would typically conditionally format the column for duplicates and then compare the two date fields. I'm struggling to come up with a solution to achieve this with Alteryx.

Any ideas are greatly appreciated!

Workflow

Output

Date Time

Transformation

Accepted answers

jdunkerley79

I would use a summarize tool to group on the ID and select the maximum date.

You can then use a join tool to join back to the input data on ID and date to max date.

The J(oin) output of the Join tool will then be a unique set of the newest records.

All comments

jdunkerley79

I would use a summarize tool to group on the ID and select the maximum date.

You can then use a join tool to join back to the input data on ID and date to max date.

The J(oin) output of the Join tool will then be a unique set of the newest records.

MarqueeCrew

@jdunkerley79,

I think that the minimum date (earliest) record is the way to go on this one. You say tomato and i say caprese.

patrick_digan

I've included a sample workflow like @jdunkerley79 described. I went with Min instead of max on the summarize tool like @MarqueeCrew. I added a summarize tool after my join to account for duplicate dates.

New Workflow1.yxmd

jdunkerley79

I went with Max as it said: to pull out the most recent date but @patrick_digan's sample is exactly how to do it

tom_montpool

You could also sort on the date field and then use the Unique tool.

The Unique record would be the most recent, the Duplicate(s) would be the older one(s).

The nice thing with Unique is that is keeps the data for the entire record together.

tom_montpool

You could also sort on the date field and then use the Unique tool.

The Unique record would be the most recent, the Duplicates would be the older ones.

The nice thing with Unique is that is keeps the data for the entire record together.

cschwartz

Thank you! This worked perfectly. I really appreciate everyone's suggestions!

MarqueeCrew

i guess that solves it. @cschwartz has broken the tie and @jdunkerley79 is the winner. I've starred his post.

al_sweets

Hi @tom_montpool ...

I am using your method you stated below. I see as part of the Unique tool though, it sorts on the fields you select in the tool's configuration.

So when does the Unique tool know to sort on the selected Unique Fields, or keep the order that is inputted in?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703