I have a table that lists multiple items by Unique ID. Each Unique ID can have multiple Modified Dates with same stage name. Wondering if it is possible to get a Max Date for each specific Unique ID.
Example.
ID | Stage | Date |
0064600000F7jG2AAJ | Complete | 4/25/2023 |
0064A00000vteytQAA | Complete | 3/17/2022 |
0064A00000we9kJQAQ | Complete | 5/5/2023 |
0064A00000xI7IVQA0 | Complete | 6/5/2023 |
0064A00000xnLnfQAE | Complete | 7/4/2023 |
0064600000F7jG2AAJ | Complete | 6/1/2023 |
0064A00000xI7IVQA0 | Complete | 7/3/2022 |
Would like to get a final result like this:
ID | Stage | Date |
0064A00000vteytQAA | Complete | 3/17/2022 |
0064A00000we9kJQAQ | Complete | 5/5/2023 |
0064A00000xnLnfQAE | Complete | 7/4/2023 |
0064600000F7jG2AAJ | Complete | 6/1/2023 |
0064A00000xI7IVQA0 | Complete | 7/3/2022 |
Tried Summarize Tool for Max Date, but I only get highest date overall, not all fields are shown.
Any help would be appreciated. Thank you.
Solved! Go to Solution.
In the Summarize tool, first Group By ID. If you want to carry the field for Stage, you will also need to Group By Stage, or you could use First.
Chris
Hi @DodgerFH ,
Here is another way of doing this using Sort and Sample tools.
Workflow
Output
Row 3 does not match with the Expected value.
But the actual should be correct, as 2022-07-03 < 2023-06-05.
Expected
Actual
Another way of doing this is
using summarize tool >> group by ID and max of date
use Unique tool to remove repeating values from ID
and join on the basics of ID
Thanks for making it simple! Still getting the hang of Alteryx, although I love the program.