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

5 - Atom



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!



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!



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.