Updating Date Fields to Remove Duplicates

Hello,

I have a data file for an upload and some of the entities have multiple entries with the same date. For the purposes of the data load I need to edit the duplicate dates to a day prior. For example, if I have an entity with two entries on 06/30/2023 I need to update one of the dates to 06/29/2023. Is there an easy way to set up this functionality within Alteryx?

Sincerely,

Jacob

Alteryx Community Question.xlsx

Alteryx Community Question Screenshot.PNG

Datasets

Developer

Developer Tools

Date Time

Common Use Cases

Accepted answers

All comments

Qiu

@jacwood
I would like to clarify what will be logic for updating the date?
Current date minus one day?

And in the snapshot, you have shared, there are 3 entries for 06/30/2023, so we will do about the 2 out 3?

jacwood

Good morning Qiu,

That is correct. The logic would be current date minus one day, and only two of the three 06/30/2023 dates would have to be updated.

Thank you,

Jacob

Peachyco

@jacwood

Two things I'd like to clarify:

How do you decide which of the multiple duplicates to update? Just by record position? Like, update all duplicates after the first?
If we rollback the other duplicates by 1 day, wouldn't you still end up with duplicates? Like, you start with three 06/30's, but then you end up with one 06/30 and two 06/29's.

jacwood

@Peachyco

1. The duplicates to be updated are determined by Unit code in Column A. If a unit code has more than one transaction date record repeating, I need to update it to a different transaction date within the same month.

2. That assumption is correct, but then we would have to update the two 06/29's from your example above and make one of those two 06/28.

jacwood

@Qiu

Qiu

@jacwood
A Tile tool can sequence the same value in the group of Unit.

1003-jacwood.yxmd

Peachyco

@Qiu 's solution works but it can lead to duplication if the "new" date already exists for the Unit Code. To illustrate:

Unit Code	Original Date	New Date
180	6/30/2023	6/30/2023
180	6/30/2023	6/29/2023
180	6/30/2023	6/28/2023
180	6/28/2023	6/28/2023

Maybe this can be solved by an Iterative Macro? I mean, keep throwing the dataset into the macro until all duplicate dates are gone? I'm also unsure of how to solve this.

Qiu

@Peachyco
I tried with an interative macro and I think it is working.
As you said, just keep throwing the data in the process until each date under same Unit is unique. 😁
Note that I did not control which data to be altered though.

1003-jacwood - r1.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703