Group by and Include Key Value

Andrew_Seaman

Here is my dataset:

Key	Color	Date
1	Red	1/1/2021
2	Red	2/1/2021
3	Red	3/1/2021
4	Blue	3/1/2021
5	Blue	4/1/2021
6	Black	1/1/2021
7	Black	5/1/2021
8	Black	2/1/2021

I would like to group by Color, return the Max Date, then include the Key with this record:

Color	Max Date	Key
Red	3/1/2021	3
Blue	4/1/2021	4
Black	5/1/2021	7

In Alteryx I'm having trouble configuring the Summarize tool to get this output, any help is appreciated.

Luke_C

Hi @Andrew_Seaman 

Here's an example (Note: I believe your answer for blue is incorrect)

1. Convert dates to alteryx date format
2. Summarize to group by color, take the max date
3. Join back to data on date and color to get the corresponding record ID

Note, if there are two records with the same date you will get two records back after the join. You could use another summarize tool to concatenate the IDs into one row.

Summarize.yxmd