I've been able to do this in Python but Python-in-Alteryx is quite painful so looking for a purer Alteryx solution if possible.
I have a set of boxes A with their own capacities, and a set of blocks B with their own capacities. There is a key that associates potential candidates from B for each box in A, so I end up with a 1-to-many join between A and B.
Note that there are overlaps, such that a block could be potentially a candidate for two or more boxes. To wit:
| box_id | box_capacity | block_id | block_capacity |
| 1 | 100 | 100 | 85 |
| 1 | 100 | 105 | 60 |
| 2 | 72 | 105 | 60 |
| 2 | 72 | 103 | 30 |
| 2 | 72 | 107 | 10 |
| 3 | 50 | 100 | 85 |
| 4 | 40 | 110 | 22 |
| 4 | 40 | 112 | 18 |
| 4 | 40 | 115 | 8 |
I need to allocate blocks to the boxes so as to match the box_capacity. So for box_id = 1 above, I can use 100% of block_id 100 and 25% of block 105 (100% * 85 + 25% * 60 = 100).
I guess I could do this with some kind of rolling sum and subtracting from the box_capacity until it's 0, and then ignoring any subsequent blocks.
But when I go to box_id = 2, I don't have a capacity of 60 for block 105 anymore - I have only 75% of 60 (=45). I'd have to assign this whole amount, then 90% of block_id 103 and 0% of block_id 107 to achieve the box capacity of 72.
Note that box 3 cannot be allocated at all as its only candidate block 100 has already been used up.
Box 4 can be filled up with 100% of blocks 110,112, and doesn't need box 115
How to cascade the remaining capacity for any block to subsequent records in the dataset to achieve such an allocation?
I'm dealing with about 5000 boxes and 500,000 blocks, and I'm ending up with hundreds of millions of potential candidate records, so an efficient mechanism will be very welcome
Please advise, thanks.
