Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Overlapping timestamp

FMG
6 - Meteoroid

Hello,

 

I have a bunch of events stored on a DB, all of which contain a start time and end time in two different columns. The events have different types, which are also stored in a column. For a given time range, I would like to get how many time of events type A are overlaping with events type B and how many time events of type A are not overlapping with any other event type. 

 

Here is a simplified example ( in my real data, I have thousands of rows and I need to be accurate to the seconds). 

 

EventsTypeStart timeEnd time
1B02:00:0004:00:00
2B04:00:0005:00:00
3A04:30:0005:30:00
4B05:30:0007:30:00
5A07:25:0007:55:00
6B07:50:0008:50:00
7B08:50:0009:00:00
8B09:00:0010:00:00

 

In this I would like to get the Overlapping A on B from 02:00:00 to 10:00:00 and A non overlapping with any other event.

 

I would get : 40 minutes overlapping A on B and  50 minutes non overlapping

 

 

Any clue how I could get that done? I have tried manipulating the data with multi-row formula, but it gets really tricky and I don't believe I will be able to get to a solution efficienctly. 

4 REPLIES 4
BenMoss
ACE Emeritus
ACE Emeritus

Here is a starting point (attached).

 

The generate rows tool is your friend here (I say this too much, but it's bloody useful when working with ranges!).

 

I actually get '47' minutes overlap. 

 

This is because each of your time slots actually shares a minute too.

 

i.e. 16:00-17:00 and 17:00-18:00 share a minute.

 

 

FMG
6 - Meteoroid

Hi Ben,

 

Thanks for your quick response.

 

This will definitely help.

david_fetters
11 - Bolide

Depending on how spread out the data set is, using Create Records may not work perfectly, especially if your time scale includes seconds.

 

I posted about the DateTimeDiff function and how you can use it to figure out if there is overlap between two time periods here:

https://community.alteryx.com/t5/Data-Preparation-Blending/Best-way-to-find-concurrent-DateTime-rang...

Hiblet
10 - Fireball

I had a similar problem and wrote a macro for it.  See "TimeMatch" on the public gallery.  You pass in two datastreams that have timestamp fields, and give the macro a tolerance timespan.  The tolerance defines what you mean by "at the same time" ie within this many seconds.  This works with fractions of a second to allow for very precise matching.

 

 

Labels