Find the value in Column X when the value in column Y is the maximum value in the column

[khuebsch5]

So I've got three fields: User ID, Department Code, and Punch Date.

The user's department codes shift over time, so I need to find the most recent code they were assigned. My thought is to identify the most recent punch date, then select the department code for that row of data, but I'm having trouble coming up with the logic to show that in Alteryx. Does anyone have thoughts on this?

Ex data:

User ID	Dept Code	Punch Date	Recent Code (Output)
1234	1105	5/4/2021	2002
1234	2002	3/15/2022	2002
1234	2002	3/20/2022	2002
1234	1106	9/22/2021	2002
1234	1105	5/27/2021	2002
1445	1105	2/15/2022	1105
1445	1108	12/9/2021	1105
1445	1108	2/7/2022	1105
1984	2003	3/27/2022	2003
1984	2003	1/30/2022	2003
1984	1105	6/19/2021	2003

Thanks!

[DavidP · Accepted solution]

Hi @khuebsch5 

My approach would be to follow the following process.

1. Convert your date to an Alteryx date with Datetimeparse([Punch Date],'%m/%d/%Y')

2. Now find the max date for each User ID with a Summarize tool (GroupBy User ID, Max date)

3. Use a join tool to join the result from the Summarize back to the original dataset, matching on User ID and Date

Have a go and see how you get on.

Happy Alteryxing!

[binuacs]

@khuebsch5 Similar to @DavidP  suggestion

[MarqueeCrew]

 

[khuebsch5]

Whoops, forgot to come back and respond. This was perfect! I knew there was an outside-the-box solution I wasn't thinking of lol