Hi all,
I'm trying to compare nine different date fields (I know) from one data set, and pull only the most-current date into a new column, MostRecentDate. My first thought was to do a series of if statements, if Date1 > Dates 2-9, and so on. Is there a faster way to get this answer, rather than brute-forcing it with if/elseif statements?
Current data:
Name | Date1 | Date2 | Date3 | Date4 | Date5 | Date6 | Date7 | Date8 | Date9 |
John Doe | 2016-04-25 | 2019-01-02 | [Null] | 2017-05-08 | 2017-08-23 | 2018-03-04 | 2018-11-07 | [Null] | [Null] |
Jane Smith | 2016-03-24 | [Null] | 2018-12-25 | [Null] | [Null] | [Null] | [Null] | 2017-12-12 | 2017-12-27 |
Goal:
Name | MostRecentDate |
John Doe | 2019-01-02 |
Jane Smith | 2018-12-25 |
Solved! Go to Solution.
You could first transpose your data so all the date columns are pivoted to rows, then you can sort on the date column, keep the most recent date and then join this set back to your original data set. In Transpose tool, you can keep 'Dynamic or Unknown Fields' option checked so any new dates coming in will be included. Attached is a sample.
Thanks! I ended up using this flow, and joining it back to my original data set, as @AnupD suggested.
User | Count |
---|---|
18 | |
15 | |
13 | |
9 | |
8 |