Hello!
I have a list of events with time stamps.
I am trying to filter out events that don't occur within two minutes of another type of event.
Any suggestions?
filter your time versus the other event. make sure both are datetime
datetime([date],[event],"minutes")<=2 is your filter.
There is no just DateTime
Are you referring to DateTimeDiff([Date],[Event],"minutes")<=2
This is great, but the events are categorical like:
Time Event
2019-06-20 09:30:00 swing
2019-06-20 09:29:00 tango
2019-07-20 08:30:00 waltz
Yes - datetiemdiff... you need to match the categorical events to a start time. Do you have a separate datasource of those? If so you can use a join to get that to join with "event"
rethinking this - you're probably looking; at a macro (batch is how i'd do it)...
you run the events through the control parameter and find events of differing types. You then use a second filter to see if there are events within two minutes using datetimediff. If you post some data - I can show you how to implement.
I think I figured out what you mean.
I used a filter to split the data between the two different event types.
Then I joined them back together so that I had two sets of data (two different event data sets) beside each other.
I then ran another filter with DateTimeDiff([Event A Times],[Event B Times],"minutes")<=2
Unfortunately, it didn't work when I looked at the times in the resulting table and it is also not doing what I need it to do.
I need to determine if there is an event A within two minutes after an event B
I am ultimately looking for ABA and BAB patterns within a time constraint.
Ok, here is some fake data..
Can I run a test to see if there is an ABA or BAB event pattern in the same location and within a 2 minute time interval?
Location, Time, Event
1, 2019-12-22 23:48:00, A
1, 2019-12-22 23:49:00, B
1, 2019-12-22 23:50:00, A
2, 2019-12-22 23:48:00, A
2, 2019-12-22 23:14:00, B
3, 2019-12-22 23:50:00, A
3, 2019-12-22 23:48:00, B
3, 2019-12-22 23:49:00, B
4, 2019-12-22 23:48:00, A
I used the Multi-Row Formula tool as you suggested (I couldn't open your file), but I have two issues:
1) The formula for checking time difference leaves a 0 at the first iteration, even though it may be 2 minutes from the next row.
IF DateTimeDiff([Activation Time],
[Row-1:Activation Time],
'minutes')<2
THEN 1
ELSE 0
ENDIF
2) The formula I am using to check for the ABA pattern cannot recognize the variables and I don't know why.
IF [Row-1:Event] = 'A'
AND [Row+0:Event] = 'B'
AND [Row+1:Event] = 'A'
THEN 1
ELSE 0
ENDIF
I didn't code anything for A,B, A so I'd have to look at your workflow to see what the error there is... I can tell you that [Row+0] doesn't exist it's just referenced in multi-row as [Event]... My version just filtered out entries which were the same type and used the others for comparisons.
So you are going to have to figure where you are starting your compares - ie row 0 to row 1 or last row to row which doesn't exist after. usually if you are using [row-1] you build in some logic life if row-1: = null() then x... else (and then you have your conditions - since you recognize there is no real comparison between the 1st row and 0th row...).
Are you running alteryx 2021.1? I can post the macro and the workflow in a zipped version but the macro is going to point my machine so you'll have to fix it. I know you are getting a warning opening my workflow - but just hit ignore a few times and it should work. In the alternative if may unzip the workflow and you can open the component parts without the overall wrapper.