Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Filling a schedule based on several criteria

Highlighted
5 - Atom

Hi,

 

I am new to Alteryx. Have never used it, haven't even downloaded it yet. I work as a scheduler for a Minor Sports Association. This past year was my first year doing this job. I managed it with a Google spreadsheet. Actually about 8 sheets that essentially made a web app that aided me in building the schedule. I'm wondering if Alteryx might be a better tool for me but its hard to search for how to fill a schedule when "Scheduling" is a big part of this program. 

 

So here is the basic flow of what I need to do with my data.

 

1) I start with a base list of all the ice time (its a hockey association) that we have for the season. It's about 300 slots booked over 7 months. The slots are anywhere from an hour long to 12 hours long. I call this list my Master Schedule, it is its own sheet.

 

2) From the Master schedule I need to break each slot into 15 minute segments. I did this in a second tab in the same workbook. I created a formula that worked through the master schedule and broke each slot into 15 min segments in chronological order. This turns 300 rows in the master schedule into 7000 rows in what I call my working schedule.

 

3) Now that I have my slots broken down into something I can work with I have to manually go through the 7000 rows and break the slots into blocks that the teams can use for games or practices. At the beginning of the season our association needs to offer a certain amount of game slots to our league each week so I go trough the working schedule and slot those game slots in first and then create a filter in another spreadsheet that references my working schedule so I can send the report to the league.

 

4) After we get our game schedule back from the league I input that into my working schedule and then I can start to build the practice schedule. I have a number of criteria I need to follow. The younger kids can't use the slots past a certain time, I can only schedule them to use one slot per day, they need to have x amount of slots per week. among other criteria. 

 

5) Once I get all that filled in I'm basically done except I need to share it with the teams. I use an app called awesome table to pull the data from my google spreadsheet and display it on our association website for them to view and filter. 

 

That's the simplified version of what my google sheets web app does. There are a host of other things that I have added to help me along the way but what I outlined above is what I am curious if Alteryx can automate for me.If not all of it, portions of it would be very helpful. I am sure I could automate the process through google sheets but my creation is already hitting the maximum of what google can handle.

 

I found this example on youtube, while its a different concept, the Idea is similar to what I need to do. The way they assing a new sales territory to a sales associate would be much like how I need to assign a team to an ice slot, however I would need to apply many more criteria above just sales opportunity and geographical location. For example for each team I need to consider, Time since last slot, time of day of the slot, slots allocated per week, no other slots that day, slots allocated per year, teams who can effectively share a slot.

https://www.youtube.com/watch?v=EFaGzO0Kc-g

 

As far as my experience / knowledge goes I am very good at solving problems within spreadsheet formulas but my coding knowledge is basic. I can usually find a piece of code that will work for my application and modify it for my means but I don't have much luck creating anything from scratch. Alteryx interest me so much because it looks to be quite powerful with little coding needed.

 

Sorry for the long post and sorry if its in the wrong spot. thank you for your feedback.

 

Stanky

Ben looks at using Spatial tools in Alteryx to make equal spacial sales territories
Highlighted
11 - Bolide

Alteryx can absolutely do this. Post a sample from the "Master Schedule" and I will attach a sample workflow that does some of the below steps as a demo. 

Highlighted
5 - Atom

Thanks for the Help Drew!!!

 

This is a link to a sample of what my Master Schedule tab looks like.

 

https://docs.google.com/spreadsheets/d/1vr5qxQOxwbFpcJ5AM_HOSA0KY2ztfUaMjjE6SNQobLw/edit?usp=sharing

Highlighted
11 - Bolide

Can you explain #3 a little more / provide an example... how long is a game or practice / how many are required?

 


@Stanky wrote:

3) Now that I have my slots broken down into something I can work with I have to manually go through the 7000 rows and break the slots into blocks that the teams can use for games or practices. At the beginning of the season our association needs to offer a certain amount of game slots to our league each week so I go trough the working schedule and slot those game slots in first and then create a filter in another spreadsheet that references my working schedule so I can send the report to the league.


 

Attached is the workflow to automate #2.  

Highlighted
5 - Atom

Thanks Drew!!!

 

So our game slots vary dependent on the age group. They are either 1 hour or 1.5 hours, we also give slots 2:15 in length to another association for games but those would be in place before we auto-populate our schedule. Each slot also needs to have a 15 minute window in between slots for the ice to be resurfaced, unless it ends at the end of our allotted time from the arena. 

 

At the beginning of the season the league gives us how many game slots of each length that we need to provide. Our season is split into segments. What I would like to be able to do is input how many slots of each length I want allocated for games for each day or range of days (ie a weekend).

 

After the game schedule is input the remaining time is used for practice slots. Practice slots can vary in length. Ideally they are no shorter than 1 hour long, and no longer than 1.5 hours. For some age groups we have 2 teams share the ice for a 1/2 ice practice. For the older age groups they require the full ice for their practices. One of the harder things to accomplish when filling out this schedule is to break up the ice efficiently so that we aren't left with any odd slots that leave practice slots too short or too long. for the groups that have 1/2 ice practices, they occasionally can have full ice practices as well. Ideally we also want teams of the same age group sharing the 1/2 ice practices. You can probably arrange this by ranking each teams ideal practice partner. 

 

Each age group (we have 5 age groups) has different restrictions as to the start and end times of their ice slots, some are restricted to only Saturday/Sunday as well. Some can only play at one of the rinks that we have ice at. We would also prefer to schedule the majority of our games at one of our arenas.

 

Basically what needs to happen next is as follows.

 

- Submit a file with the pre-booked slots that we have given to other organization. 

- Have those time slots assigned to the schedule

- Submit a file with the game slots that I need with indication as to what window they need to be allocated in as well as any restrictions.

example Qty 5 - Length 1:30 - 12/5/2019 5:00pm - 12/7/2019 11:50pm - Arena = Any - start  time > 12:00

- have those time slots assigned to the schedule.

- have the rest of the schedule broken into practice slots. I will need to select what segments of the schedule get broken up as we get our game schedule about 2 weeks before each schedule segment i have to be able to limit what portion of the season the practice slots are being split up for. 

 

Hope that clarifies it a bit.

 

Thanks again.

Highlighted
5 - Atom

another thing I should mention is that when we get our game schedule we also get our road game schedule. So our teams can't practice on days that they have a road game.

Highlighted
11 - Bolide

To accomplish the above, you are going to need a batch macro and then a simple workflow to trigger the macro to run iterations for each game / practice. There are a large number of post and videos about how to setup a batch macro so I will not cover that here, but I attached the "Guts" of the macro along with a detailed description of each step. The portion in red can be updated based on your preferred criteria and the boxes outlined in red will be the input/output slots for the macro. There is sample data in the workflow so that you can see the flow is fully working to accomplish your goal   

 

Please considered marking this workflow the accepted solution if it answers your question. For more detailed help on setting up the batch macro, please post in the "Designer Discussions". 

 

Thank you,

Drew Davis 

Labels