How long is a specific activity in an available timeslot

[MichielRauws]

Hi there, 

Would love to have some suggestions on how to perform the following actions.

I have a list of actions of which I would like to know whether and for how long they fall within any available timeslot.

The start & end times of the action are overlapping and the available timeslots are different each day.

On the example below I have two questions:

• Which start of the match falls within a open timeslot?
• How many hours of each match can be aired on television?

Let's assume the timeslots on tv can be used for multiple matches, so we'll have some kind of dual screen / altering channel (but the hours do fully count).

Sports Matches:

Sport Type	Unique Number	Match Start	Match End
Tennis	#1	12:00 day 1	13:00 day 1
Tennis	#2	12:30 day 1	14:30 day 1
Tennis	#3	13:00 day 1	14:00 day 1
Tennis	#4	12:00 day 2	14:00 day 2
Tennis	#5	12:30 day 2	13:00 day 2

Television Schedule: 

Channel Type	Start Timeslot	End Timeslot
Tennis	12:00 day 1	12:30 day 1
Tennis	13:00 day 1	15:00 day 1
Tennis	12:30 day 2	13:00 day 2
Tennis	13:30 day 2	15:00 day 2

Ideally, Alteryx has a kind of Timeline function, where I could put the Start Timeslot and End Timeslot for each Channel Type, but I haven't found it yet.

I think I need some kind of loop that checks for each unique tennis match, whether the Match Start and Match End fall within Timeslot 1, if not, then try Timeslot 2, etc. 

Next step would be to check for just the start or end times and go from there.

Eventually I would like to do this analysis for many different sports, so not just tennis, but any help on this example is much appreciated!

Thanks a lot for reading and thinking with me. In case you believe I'd better look outside of Alteryx, would love to know your suggestions.

Kind regards,

Michiel

[AndrewSu · Accepted solution]

Hello @MichielRauws ,  please see the attached workflow for one way to calculate those values.

I basically formatted the dates/times and then ran different filters AFTER appending all possible combinations. 

If this helps you resolve your issue, please mark this post as the solution so that others in the community can benefit from our collaboration. 

Thanks!

[MichielRauws]

Apologies for the late response, but thank you very much!

This is exactly what I needed.

Thanks!