Updating Date Fields to Remove Duplicates

[jacwood]

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

[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:

1. How do you decide which of the multiple duplicates to update? Just by record position? Like, update all duplicates after the first?
2. 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.

[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.