Find minutes between overlapping intervals

[mystasz]

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. 

[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/142...

[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. 

[JarekSkudrzyk · Accepted solution]

@mystasz here you go

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

[mystasz]

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

[JarekSkudrzyk]

🙂 thanks for kind words:)