Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Duplicate identification based on conditions

AbhijeetChib
8 - Asteroid

Good Day Everyone ! 

 

I have a dataset where I need to identify duplicates based on some conditions and use the oldest date for Tie breakers. Here is the Data 

From a set of invoice numbers, I need to identify only AB/*. For all records with invoice numbers starting from AB we need to eliminate the duplicates while not touching all other formats of invoice numbers. 

If there are multiple duplicates identified then i need to select the record that has the last modified date. 

Here is the sample data

 

Invoice number Modified Date 
CA/1200123423.03.2023
CA/1200123423.03.2023
BD/12000123423.03.2023
AP/12000123523.02.2023
AB/12000123523.03.2023
AB/1234567823.03.2023
AB/1234567823.02.2023
AB/1234567823.03.2023
AB/1234567823.03.2023
AB/708000075623.02.2023
AB/708000075623.03.2023

 

In the above dataset, invoices starting with CA,BD,AP do not need to be touched. For the AP ones, we need to identify the duplicates and for multiple duplicates the record with the oldest date needs to come through. In the above example, the below records need to come through. 

AB/1234567823.02.2023
AB/708000075623.02.2023

 

Thanks for your suggestions. 

3 REPLIES 3
ShankerV
17 - Castor

Hi @AbhijeetChib 

 

One way of doing this.

 

ShankerV_0-1680166331426.png

 

 

Many thanks

Shanker V

DataNath
17 - Castor
17 - Castor

Hey @AbhijeetChib, here's how I'd tackle this:

- Isolate AB records

- Parse the date into ISO format for Alteryx to be able to work with it

- Summarize to Group By [Invoice Number] and pull out the minimum (oldest) date

- Reformat the date into your original display

- Union the handled AB data back to the rest of your data

 

DataNath_0-1680166098733.png

Raj
16 - Nebula

sol

Labels