Mutli-row and updating several columns

Question

Hi everyone,

I've looked into mutlirow and macro tools but I can't find a way to fix my issue.

I've got:

- date (all the values are known)

- sells (all values are known)

- starting_inventory (value of day 1 is known)

- ending_inventory (value of day 1 is known)

- shipment (value of day 1 is known)

- min_inventory (all the values are known)

- min_order_size (all values are know)

For the first date, I have all these fields filled up, so no issues for my intialization.

For every single day after this I need to do:

- starting_inventory = ending_inventory[-1] (-1 here means the day before)

- ending_inventory = starting_inventory - sells + shipment[-1]

- shipment = max(min_inventory - ending_inventory, min_order_size) (I want to order enough so that I have at least my min_inventory, technically it would be the min_inventory of the next day, but here it's OK to proceed like this)

I would therefore need to edit both the columns of ending_inventory and shipment. Days have to be calculated sequentially and can't be vectorized. My last resort is to call a Python script but I want to make sure there isn't an Alteryx way of doing it.

Is there a way to do this?

Thanks!

CoG · Accepted Answer

Do you have any sample data? that would be very helpful. An iterative macro is one way to solve the problem, although you should also be able to do this with the Multi-Row Formula Tool. If I recall correctly, there was even a weekly challenge problem very similar to this! I'll update this if I can find the link.

(Edit) Weekly Challenge: Challenge #432: Sequential Logic - Alteryx Community

CoG · Accepted Answer

Here is a sample workflow that should do what you need it to do, based on the description above.

Hope this helps and Happy Solving!