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.

Drivers Needed by Hour.xlsx

Desired Output.xlsx

rzdodson

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

Driver Schedule Workflow.yxzp