Alteryx designer Discussions

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

Purging Unique records within 5 minutes of each other in a Time field

Highlighted
5 - Atom

Hi

 

I have a dataset that includes a Date/Time field in the format yyyy-mm-dd hh:mm:ss   (e.g. 2017-09-12 15:12:00)

I need to purge 'unique' records, keeping only records that are not within five minutes of each other.

 

As an example, one record might have date/time 2017-09-11 23:59:00  and another record 2017-09-12 00:03:00

I don't care which record I retain, I'm trying to count unique events (unique defined as none other existing within five minutes)

 

Any help would be greatly appreciated!

Andy

 

Highlighted
14 - Magnetar
14 - Magnetar

Try a Multi-Row tool! Set up the formula to calculate the DateTime Difference between the current row and the next row based on minutes, and then filter for any that are >5 or null (to include the last entry in the list).

 

Formula for Multi-Row Tool: DateTimeDiff([Row+1:DateTimeField],[DateTimeField],"minutes")

 

Formula for Filter Tool: [TimeDifference] > 5 || IsNull([TimeDifference])

 

Hope that helps!

Cheers,

NJ

Highlighted
Alteryx Certified Partner

I had the same idea as @NicoleJohnson

 

I thought I can try an another way using floor and ceiling method. I moved all 1,2,3,4,5 minutes to floor(zeroth minute) and 6,7,8,9 to ceiling(10th minute) and left 10 as it is. Attached workflow.

 

 

Purge_date.pngpurge.png

Labels