community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

How to take max of a column and add records from other file?

Atom

Hi All,

 

I have 2 files:

File 1: 

SeriesIDUpdate_tsCreate_tsOrder
1MAJ3/4/19 8:003/4/19 8:10Create
2FCS4/4/19 0:004/4/19 0:10Create
3DAB4/6/19 3:004/6/19 3:20Cancel
4DAB2/4/19 15:342/4/19 15:34Create
5MAJ5/5/19 17:345/5/19 17:44Cancel

 

File 2:

IDEnroll/Cancel
MAJEnroll
FCSCancel
DABEnroll

 

Output Required:

IDMax_Update_tsCreate_tsOrderEnroll/Cancel
MAJ5/5/19 17:345/5/19 17:44CancelEnroll
FCS4/4/19 0:004/4/19 0:10CreateCancel
DAB4/6/19 3:004/6/19 3:20CancelEnroll

 

Output File has all the unique IDs with latest Update_ts.

 

I would highly appreciate if anyone can suggest the alteryx flow for this?

 

Many Thanks,

Shikha

 

Alteryx
Alteryx

Hi @sjain34!

 

See the attached workflow but basically I joined the two based on ID, converted Update_ts to an Alteryx datetime format, sorted the new Update_ts_Out column in descending order, used the sample tool to select the first 1 row and grouped by the ID field. Hope that helps! 

Labels