Find minutes between overlapping intervals

Hello Community!

I'm looking to create a new column called "Minutes" that would compare each row against every other row to determine the minutes difference for the ones that overlapped. I used Python to determine if a time was overlapping based on "Provider Name", "Start" and "End Times". If "Overlapped?" = "Did not overlap", minutes can return 0 since we don't really care about it.

In python, I would do the below to find the overlapping intervals. Now that we've purchased Alteryx, I'd like to do the minutes part here. Just can't figure it out...

df['Overlapped?'] = False
for i, row in df.iterrows():
    start, end = row["Start"], row["End"]
    df.loc[(df['Providers Name'] == row['Providers Name']) 
            & (((df["Start"] >= start) & (df["Start"] <= end)) |
                      ((df["End"] >= start) & (df["End"] <= end)) |
                      ((end >= df["Start"]) & (end <= df["End"])) |
                      ((start >= df["Start"]) & (start <= df["End"]))), 'Overlapped?'] = True

The use case for this new output would be that a user would filter on Minutes where Minutes is >= 15 and consider that as a TRUE overlap. I tried DateTimeDiff([Row+1:Start], [End], "minutes") within a Multi-Row Formula which gets close to what I want.

I'm attaching a file where the first tab is my sample data and the second tab is the expected output. I'm fairly new to Alteryx so if you could attach a workflow with your suggestion so I can follow along, it would be much appreciated!!

Thank you in advance.

Overlap Minutes Expected Output.xlsx

Python

Date Time

Common Use Cases

Transformation

Preparation

Accepted answers

JarekSkudrzyk

@mystasz here you go

not a very tidy workflow but hopefully it gets the job done - at least on the examples given:)

Overlaps_v2.yxzp

All comments

JarekSkudrzyk

@mystasz here you go.
Please check especially the output for date 04.10.2021 and provider JOHNSON - I am afraid I did not understand where the expected amounts come from.

I have based my solution on this article:
https://community.alteryx.com/t5/Weekly-Challenge/Challenge-106-How-Long-were-the-Lights-On/td-p/142023

Overlaps.yxzp

mystasz

Hi @JarekSkudrzyk

I know this is a bit weird since evaluating the previous row wouldn't work. For the case for Johnson:

1) The first case Starts at 12 AM and ends at 7:53PM

2) The second case starts at 8:05 AM and ends at 8:47 AM which overlaps with the first case

3) The third case starts at 9:55 am and ends at 10:36 AM which overlaps with the first case but not the second case

4) The fourth case starts at 1:19 pm and ends at 2:15 pm which overlaps with the first case but not the second or third case

5) The fifth case starts at 4:39 pm and ends at 5:07 pm which overlaps with the first case but not the second, third or fourth case

Overall, all the cases below the first overlap with the first only. So the minutes calculation would be from the End of the fist and the start of all the others.

The issue is that since this scenario is so odd and the others seem to be in order (row + 1), the logic of using the previous row isn't working out. it's like jumping back and forth to see which one it overlapped with.

In the case for John:

1) The first row overlaps with the second by 2 min

2) The second row overlaps with the 3rd row by 20 min

3) The third row overlaps with the 2nd row by 20 min

JarekSkudrzyk

I see.

So for Johnson is the answer provided by the workflow ok for you? For me it seems like it is - total of all other rows is 167, so the overlap cannot be longer than the sum of these other rows.
And for John should the second row have 22 mins (20 min with 3rd row + 2 min with 1st row)?

mystasz

Unfortunately the answer provided by the workflow for Johnson wouldn't work out.

1) The first row overlaps with the second by 708 min since the second one started at 8:05 am, about 11 hours earlier than the first one ended

2) The second row overlaps with the first row by 708 min as well

3) The third row overlaps with the first row by 598 min. It started at 9:55 AM, about 9 hours earlier than the first

For John, I wouldn't sum it. It would compare the second row against the 3rd and the 3rd row against the second. Making them both 20 min.

😕 sorry...there's too many scenarios outside the norm but your workflow is definitely helpful so I will keep trying

JarekSkudrzyk

I think I start to get what is expected:)

So first of all the workflow should determine which rows overlap with each other.

Then it should calculate the difference between the end time of the overlapping period that started first with the start of the other period?

In case a period overlaps with more than 1 other period - the "Minutes" column should show the higher number of minutes between end of the last ending period and start of the other period?

mystasz

I already computed the rows that overlap (column "Overlapped?"). I'm attaching a new file which contains another column for greater than 2 overlaps (column "Greater than 2 overlaps").

If i'm understanding your sentence correct, then yes. The Minutes column should show the higher number of minutes between the end of the last ending period and start of the other period.

Your workflow works wonderfully for the ones that were 2 overlaps btw. The greater than 2 ones are tricky. Hope this clarifies!! The expected output is on the second tab.

Overlap Minutes Expected Output V2.xlsx

JarekSkudrzyk

@mystasz here you go

not a very tidy workflow but hopefully it gets the job done - at least on the examples given:)

Overlaps_v2.yxzp

mystasz

@JarekSkudrzyk You are a genius!!!!!!! Thank you so much.

JarekSkudrzyk

🙂 thanks for kind words:)

mystasz

Hi @JarekSkudrzyk,

I've been struggling a bit to get this solution to work on my actual data and maybe I'm not understanding something. I'm hoping an explanation would get me back on the right track. At the end of the "Determine which records overlap" container, you put in a Text to Columns tool which split the values into two separate columns: Record_1 and Record_2.

I can't tell why my values have more than 2 values which forces me to change "Number of Columns to +10" where I have to join 10+ Record IDs at the end. Any idea where I could be going wrong?

JarekSkudrzyk

@mystasz

Hi,
if you have more than 2 values after text to columns it probably means that for this time period there are more than 2 overlapping records.
If you provide an updated sample I can try to figure out how to update the workflow to make it work.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335