Hi,
I have some data looking like this
id | year | date |
1 | year 1 | 2015-09-30 16:33:53.000 |
1 | year 2 | 2016-09-30 16:33:53.000 |
1 | year 3 | 2017-09-30 16:33:53.000 |
2 | year 1 | 2015-09-30 16:33:53.000 |
2 | year 2 | 2016-09-30 16:33:53.000 |
2 | year 2 | 2016-10-30 16:33:53.000 |
2 | year 3 | 2017-09-30 16:33:53.000 |
For each year, I only want to choose the latest date within the year. That means, I wish an output of date, that looks like this
id | year | date |
1 | year 1 | 2015-09-30 16:33:53.000 |
1 | year 2 | 2016-09-30 16:33:53.000 |
1 | year 3 | 2017-09-30 16:33:53.000 |
2 | year 1 | 2015-09-30 16:33:53.000 |
2 | year 2 | 2016-10-30 16:33:53.000 |
2 | year 3 | 2017-09-30 16:33:53.000 |
In a programming language, I would loop through each year for each id, and choose rows based on the latest date. But do you guys have any idea on how to do this easily in Alteryx? Thank you very much in advance.
Solved! Go to Solution.
Use the Summarize tool and group by Id, Year and Max of date
You can pass your input to a summarize tool, and use the following commands:
Group By: ID, Year
Max: Date
Note that it will be necessary for your Date column to be a DateTime type in order to perform the MAX operation.
Here's a screenshot: