Using Group By to Select Max Date

[MapperKnime]

Hi,

I am trying to select the maximum date in one field for a unique identifier in another field
Current Spreadsheet
Unique ID Date
1 6/1/2010
1 1/2/2013
1 4/1/2000
1 7/9/2017
2 9/13/2015
2 8/8/1972
2 8/29/1999
2 3/27/2018
3 1/1/2018
3 12/15/2017
3 4/1/2013
3 2/18/2000
4 5/1/2000
4 1/6/1999
4 2/27/2018
4 4/27/2014

Desired Result
Unique ID Date
1 7/9/2017
2 3/27/2018
3 1/1/2018
4 2/27/2018

I looked through some other threads and saw suggestions to use the Group By node and then a row filter. I could not figure out how to configure the Group By node -

Looking for suggestions

Thank you!

[s.roughley]

In the ‘Groups’ tab, Add your ‘Unique ID’ column. In the ‘Manual Aggregation’ tab, add the ‘Date’ column, and then in the dropdown which appears next to it, select ‘Maximum’

Steve

[ScottF]

This is close, but @MapperKnime will need to convert the dates to Date&Time format first, perhaps using the String to Date&Time node. Otherwise the maximum aggregation in the GroupBy node returns an incorrect answer.

I threw together this quick workflow DateConversionGroupByExample.knwf (8.7 KB):

which returns the following as output:

Note that I changed the format of the input date slightly to play nicely with the String to Date&Time node.

[s.roughley]

@ScottF - I was assuming they were already date cells hence not mentioning that - thanks for the extra clarification!

Steve