Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Compare Multiple Date Fields

Highlighted
7 - Meteor

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:

NameDate1Date2Date3Date4Date5Date6Date7Date8Date9
John Doe2016-04-252019-01-02[Null]2017-05-082017-08-232018-03-042018-11-07[Null][Null]
Jane Smith2016-03-24[Null]2018-12-25[Null][Null][Null][Null]2017-12-122017-12-27

 

Goal:

NameMostRecentDate
John Doe2019-01-02
Jane Smith2018-12-25
Highlighted
8 - Asteroid

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. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @agriese 

 

Pretty easy using transpose tool!

 

capture.PNG

 

WF attached.

 

Cheers,

Highlighted
7 - Meteor

Thanks! I ended up using this flow, and joining it back to my original data set, as @AnupD suggested.

Labels