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: IDNameLastUpdate89Ken2016-12-12 08:34:0189JIM2016-12-12 08:31:55 Expected output IDNameLastUpdate89Ken2016-12-12 08:34:01

Filter data to returned most recent date based on particular column

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

Common Use Cases

Accepted answers

NickJ

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

Date Filter.yxmd

All comments

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

Date Filter.yxmd

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.

NickJ

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

Date Filter.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335