Filter data to returned most recent date based on particular column

[arsenal88]

Hi I'm new to Alteryx, and wonder how can I select only those based on below scenario? Basically it based on ID and LastUpdate.

records:

ID	Name	LastUpdate
89	Ken	2016-12-12 08:34:01
89	JIM	2016-12-12 08:31:55

Expected output

ID	Name	LastUpdate
89	Ken	2016-12-12 08:34:01

[NickJ]

Hi,

Welcome to Alteryx!

Just to confirm what you're looking for - you're looking to filter the data to return the most recent date found in the LastUpdate field, correct?

A really simple way to achieve this would be to add a Sort tool, sorting on LastUpdate - Descending. Add a Sample tool with First N Records (N=1) to retrieve the latest date. 

If that's not quite what you were after, please reply and add a little more detail?

Cheers!
Nick

[arsenal88]

I hv multiple rows with different ID. If same ID then i want to extract those hv lasted date.



Sent from my Samsung Galaxy smartphone.

[NickJ]

Hi,

Are you able to add a few more rows of sample data to explain your use-case?

I've added a few below:

ID	Name	LastUpdate
89	Ken	2016-12-12 08:34:01
89	JIM	2016-12-12 08:31:55
90	Nick	2016-12-11 08:31:45
90	Pete	2016-12-11 09:31:55
91	Barb	2016-12-12 10:12:34
96	Jon	2016-12-31 23:45:12
99	Carl	2017-01-02 14:23:45

Are you looking for the LastUpdate value for each ID (and in the case where there's more than one row per ID, you want the latest LastUpdate value only)? 

If the above is correct, are you looking for:

ID	Name	LastUpdate
89	Ken	2016-12-12 08:34:01
90	Pete	2016-12-11 09:31:55
91	Barb	2016-12-12 10:12:34
96	Jon	2016-12-31 23:45:12
99	Carl	2017-01-02 14:23:45

Cheers,

Nick

[arsenal88]

Exactly



Sent from my Samsung Galaxy smartphone.

[arsenal88]

Exactly



Sent from my Samsung Galaxy smartphone.

[NickJ · Accepted solution]

Great - thanks for clarifying. 

In that case, it's the same workflow as above, but you now add a Group to the Sample tool (I've attached an updated workflow). You group by ID so that you get the Top 1 (per ID value). 

Let me know if that works for you?

Cheers,

Nick

[arsenal88]

Excellent. Thanks

[JJH]

Hi,

it seems your 2nd workflow is the same as the 1st one. 

If I were to change the table to look like the below, how would you pull 1 row of data per User where the 'Date' is most recent?

Data Table

User	Login Dates
A	2020-01-30
B	2007-05-09
A	2019-12-31
C	2020-03-14
B	2020-02-15

Required Output

User	Login Dates
A	2020-01-30
B	2020-02-15
C	2020-03-14

[JJH]

I seem to have figured it out..

Use 'Summarize' tool.

in the tool, group by 'User' & max of 'Date Login'.