Alteryx Designer Discussions

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

Best way to find concurrent DateTime ranges

timryanwalsh
5 - Atom

I am currently working with a dataset that has two DateTime variables - BeginningTime and EndTime 

 

I am looking to compare these variables among all the rows of my dataset. I want to determine which rows have time ranges that overlap with each other. 

 

What is the best way to do this in Alteryx?

2 REPLIES 2
IanCo
Alteryx
Alteryx

Hi @timryanwalsh,

 

It depends on what end result you are looking for. But the basis of the calculation would be a calc that takes into account all the scenarios of the BeginningTime and EndTime of overlap. Meaning, dates would overlap if:

1. One of the BeginningTime or EndTime was in between the original times

2. Both of the BeginningTime or EndTime are in between the original times

3. The BeginningTime or EndTime surround (one before, one after) the original times

 

You can write these three logic statements into a calc if you have a date in question and wanted to see which dates in your dataset overlapped those.

 

The other option, if you are trying to see every dataset that ever overlaps, is to use the summarize tool to take the Max (EndTime) and Min (BeginningTime) of the dataset and use the above logic to compare.

 

david_fetters
11 - Bolide

So if each record has a time range, and you wish to compare all ranges against one another, you'll first need to get all four timecodes (Begin1, End1, Begin2, End2 all properly set as DateTime fields) into individual records.  The easiest way to do this is to use the Append tool (make sure to allow it to append as many items as possible).  This will cartesian join all of your fields (e.g. 10 records turns into 10 * 10 = 100 records).

 

First, add a record ID field and then put your data stream into both sides of the append tool.  We have added the record ID so we can filter out cases where RecordIDs are the same and thus the row has been appended to itself.

 

The real trick is using the DateTimeDiff function to create a statement that is true in one of two cases: 1) the Begin2 time is between the Begin1 and End1 time, or 2) the End2 time is between the Begin1 and End1 time.  The following formula will return 1 if either of those cases are true:

IF (DateTimeDiff([Begin2],[Begin1],"seconds") > 0 &&
DateTimeDiff([Begin2],[End1],"seconds") < 0) ||
(DateTimeDiff([End2],[Begin1],"seconds") > 0 &&
DateTimeDiff([End2],[End1],"seconds") < 0) THEN 1 ELSE 0 ENDIF

If you want to permit cases where End2 = Begin1 or Begin2 = End1, just change the respective >< symbol into a >= or <=.  The DateTimeDiff subtracts the DateTime field listed second from the one listed first, returning the difference in the specified units.  So, in DateTimeDiff([TimeOne],[TimeTwo],"seconds"), if TimeOne is after TimeTwo, the result is >0, whereas if TimeTwo is after TimeOne, the result is <0.

Labels