Assigning jobs based on a weekly crew capacity macro?

[chadosu]

Hello experts,

I have 2 datasets - 

The backlog of jobs, the hours for each job, and the week all material will be ready to work:

RecordID	Job Number	Hours	Ready Week
1	7716856	11	202407
2	7716856	12	202407
3	7713693	40	202409
4	7716442	19	202410
5	7714624	62	202410
6	7714624	174	202410
7	7715866	205	202410
8	7715867	153	202410
9	7711139	20	202410
10	7711139	30	202411

And a weekly crew capacity:

Scheduling Week	Capacity (hours)
202405	500
202406	500
202407	500
202408	500
202409	500
202410	500
202411	500

With consideration of the Ready Week, I am looking to assign these jobs and adjust capacity, and update the 2 original tables like this:

RecordID	Job Number	Hours	Ready Week	Scheduled Week
1	7716856	11	202407	202407
2	7716856	12	202407	202407
3	7713693	40	202409	202409
4	7716442	19	202410	202410
5	7714624	62	202410	202410
6	7714624	174	202410	202410
7	7715866	205	202410	202410
8	7715867	153	202410	202411
9	7711139	20	202410	202410
10	7711139	30	202411	202411

On record 8, you can see the job was too large for the remaining capacity and scheduled for the next week, while the smaller job on row 9 was able to fit into the remaining week 10 capacity.

Scheduling Week	Capacity (hours)	Adjusted Capacity
202405	500	500
202406	500	500
202407	500	477
202408	500	500
202409	500	460
202410	500	20
202411	500	317

Any help or direction on this is greatly appreciated.

Thank you!

[Qiu · Accepted solution]

@chadosu 
It appears to be a Bag Split issue to me .

Hope this one can help.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/HELP-How-to-use-ITERATIVE-MACR...

[chadosu]

Thank you for the reference. This would seem to work when not considering the "Ready Week". Trying to understand how to build that condition in.

[chadosu]

I just had to add in the Ready Week logic to the multi-row tool and got my desired result!

Thanks!

[Qiu]

@chadosu 
Thank you for the feedback and glad to know it is resolved.

[ladazhome]

Is it possible that you post the final version adapting it to your problem?

[chadosu]

Here is the workflow.

A couple of notes on the input data - I have included the weekly capacity (500 hours) as well as the week I want the scheduling to start (Sched Start Week).