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

[JoBI13]

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 ID	Customer Record Number	Purchase Year	Customer Status
1005	1	2015	Active
1005	2	2016	Not Active
1005	3	2016	Not Active
1005	4	2017	Active
1005	5	2017	Not Active
1006	1	2016	Active
1006	2	2017	Not Active
1006	3	2017	Active

[lmorrell]

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.

[JoBI13 · Accepted solution]

Hi Imorrell,

Thank-you sooo much that was very helpful 🙂

[patrick_digan]

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

[Carlinhos]

Helped me a lot, thanks