Creating Weekly Schedules

[matthewmanning]

I'm working on creating an Alteryx flow that will allow me to produce weekly driver schedules at distribution centers I am responsible for. I have an initial workflow that outputs the number of drivers needed by hour (attached is a sample/example of this data "Drivers Needed by Hour").

I'd like to use this initial output and somehow create schedule that will loop and add in drivers and their hour counts similar to the format of the attached sample/example "Desired Output". One thing to note is that I would like to put a threshold on how many hours the drivers can work in a week (i.e. max 10 hours per day / 50 hours per week).

I think I could use some sort of macro to do this. Any help would be greatly appreciated.

[rzdodson]

@matthewmanning do these drivers have set start and end times for their shift, or do you just need to plug them in starting with Driver 1 until 10 hours is reached then Driver 2 for 10 hours where there is still hours to be covered? Also, do these schedules vary for each driver by-day, or is the schedule represented in the Drivers Needed by Hour input static/fixed?

[matthewmanning]

@rzdodson thanks for the reply.

There are no set start/end times for the drivers, just need to plug them in starting with Driver 1 until 10 hours is reached then Driver 2 for 10 hours where there is still hours to be covered. Also, the 10 hours is just a maximum they can work in a day. If there needs to be a driver work less than 10 hours, that is ok as well.

The schedules will vary depending on the initial data set used that indicates drivers needed by hour. I just need an example work flow to use to see how this can be created.

[rzdodson · Accepted solution]

@matthewmanning this was a pretty challenging workflow - first time really dealing with iterative macros - but hopefully I have a great solution for you.

Here is what the workflow looks like.

Beneath the main workflow, you will see a light blue container that shows the test output from the iterative macro against the original sample.

I've included tool notes throughout the workflow so you can see the why of the iterative macro. Basically what occurs is that we organize the data by driver and by working hour to get a sequential list. From there, we:

1. Assign all working hours for the driver,
2. Subtract from the number of drivers that we need for the hour if there was a driver assigned within that hour, and then
3. Join that decremented data set against the original. By doing that join, we get a new number of drivers needed for the hour

What falls out of the macro is the driver's assigned schedule, and then repeat the process until all drivers are assigned.

The one callout worth mentioning in the iterative macro is the use of an iteration number variable with the Engine.Iteration constant. The iteration number variable (Iteration) is simply just the unique driver number - I used a RecordID tool to assign that unique number to each driver. The Engine.Iteration constant variable becomes available when we select the macro type we are building from the Workflow - Configuration menu. That Engine.Iteration variable represents the number of times we are needing to loop; but, the Engine Iteration number always starts at 0. Since we don't have a Driver 0, I added +1 to the Engine.Iteration variable so we can get the correct number of iterations for the number of drivers we are assigning (1 = Driver 1, 2 = Driver 2, etc.).

If this helps, please make sure to accept as a solution so it can help users on their future workflows. :)