We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

How do I choose the most recent record if there are duplicates ?

JoBI13
6 - Meteoroid

Hello everyone,

 

I'm new to Alteryx and would really appreciate some help with my query below.

 

I have a long table of over 10,000 customer records and I have previewed a few examples from the table below. The entire table contains some duplicate customer records for the same customer within the same Purchase Year. For example, a customer might have more than one customer record if they change customer status within the year. In Alteryx I want to retrieve a list of all customer records for the most recent record number for a particular Purchase Year  E.g. If I added a Filter Tool to the workflow and filtered on Year 2017, then row 5 should be in my list for Customer ID 1005 (record number 5) and but if I filtered on Year 2016 then row 3 would be in the table for Customer ID 1005. So essentially, I want to produce a separate table with only the most recent customers records for each purchase Year and customer ID.

 

Please can someone explain how I can apply this rule to my entire table of 10,000 customer records as I'm not sure what tools to use to achieve this.

 

Many thanks.

 

Customer IDCustomer Record NumberPurchase YearCustomer Status
100512015Active 
100522016Not Active
1005 3
2016
Not Active
100542017Active 
100552017Not Active
100612016Active 
100622017Not Active
100632017Active 
4 REPLIES 4
lmorrell
11 - Bolide

Hi @JoBI13

 

Solution is attached.

 

This scenario can seem daunting, but is perhaps one of the greatest use cases for the summarize tool. If all your data is ordered nicely by record number, we can summarize the table to return only the last Customer Record Number and Status for each Customer in each Year.

 

From there you can filter on Purchase Year to return a list of all customer's most recent record within each year.

 

Snag_9d2294e.png

Snag_9d19d88.png

 

JoBI13
6 - Meteoroid

Hi Imorrell,

 

Thank-you sooo much that was very helpful 🙂

 

 

patrick_digan
17 - Castor
17 - Castor

@JoBI13 For what it's worth, sorting and then using the unique tool is another great way to solve this problem, especially if you have more columns of data. The sort gets the data ordered properly and then the unique grabs the first record for every customer ID and Purchase Year.Annotation 2019-09-05 084035.jpg

Carlinhos
5 - Atom

Helped me a lot, thanks

Labels