Select Specific Record based on Reason Code

dmill8023

Good afternoon all!

I am trying to return from my data set the oldest, most recent, instance of a reason code for an order # in my data set.  I have an example below - essentially my order # has a reason code that can change daily.  I am trying to find the difference in today's date from the oldest Run Date of the most recent run of "XX Reason Code". 

So essentially, in my data set below I want to find the difference in today's date to Order 1001, Reason Code XX, on Run Date 10/9/2022.  I initially tried a Summary tool on MIN Run Date Order by Order, but ran into issues when I had Reason Code XX then a run of different Reason code and then need the "min Run Date" of the new run of Reason Code

Order	Reason Code	Run Date
1001	XX	10/11/2022
1001	XX	10/10/2022
1001	XX	10/9/2022
1001	NI	10/8/2022
1001	NI	10/7/2022
1001	XX	10/6/2022
1001	XX	10/5/2022

DataNath

@dmill8023 is this along the lines of your requirement? The workflow:

1) Formats your dates into a form that Alteryx recognises, so that they can be ordered (in case they're not already) and the days difference between today's date measured

2) Orders the dates descending (latest to earliest)

3) Assigns a group so that if the [Reason Code] changes and then goes back to a one seen previously, they're classed as different instances

4) Isolates 'Group 1' so that you're only getting the most recent [Reason Code]

5) Finds the minimum date within this 'Group 1' and returns the data related to it

Find oldest, most recent reason.yxmd