Comparing the Dates and retrieve the latest value

[RonD]

We have a search that populates a csv file for tracking purposes of latest check-ins formatted as (%m/%d/%Y)

Host	agent1_date	agent2_date	agent3_date	agent4_date	agent5_date
Asset1	11/16/2020	11/15/2020	11/16/2020	11/13/2020	11/16/2020
Asset2	11/15/2020		11/13/2020	11/13/2020	11/13/2020

How do I go about on comparing all these dates and get the latest value and write it to a new column - latest date

Expected outcome:

Host	agent1_date	agent2_date	agent3_date	agent4_date	agent5_date	latest_date
Asset1	11/16/2020	11/15/2020	11/16/2020	11/13/2020	11/16/2020	11/16/2020
Asset2	11/15/2020		11/13/2020	11/13/2020	11/13/2020	11/15/2020

[RonD]

Thank you, this is actually working well for my requirements!

[rnowitzki · Accepted solution]

Hi  @RonD ,

Maybe not the most dynamic solution, but it should work:

|  eval latest_date=strftime(max(strptime(agent1_date,"%m/%d/%Y"),strptime(agent2_date,"%m/%d/%Y"),strptime(agent3_date,"%m/%d/%Y"),strptime(agent4_date,"%m/%d/%Y"),strptime(agent5_date,"%m/%d/%Y")),"%m/%d/%Y")

The strptime and strftime is to convert it to epoch and back to "humand readable" in order to make the dates comparable.

Hope it helps
BR
Ralph