Event occurrence, time threshold

[sartol]

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?

[apathetichell]

filter your time versus the other event. make sure both are datetime

datetime([date],[event],"minutes")<=2 is your filter.

[sartol]

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

[apathetichell]

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"

[apathetichell]

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.

[sartol]

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.

[sartol]

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

[apathetichell]

I had an error in my macro - ill reup with your data when I have it working... This one is set up for your sample data... you can set up a BAB or ABA pattern in multi-row formula fairly but this workflow wasn't designed for that specifically.

[sartol]

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

[apathetichell]

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.