Mutli-row and updating several columns

[dantes007]

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!

[cmcclellan]

It feels like a multi-row tool and then a formula tool, but maybe you need to create a macro.

Can you post some sample data and the expected result?

It's really hard to find something where you must use Python because Alteryx can't do it, but maybe ....

[CoG · Accepted solution]

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 solution]

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!

[dantes007]

Wow. That is super slick. Thanks for being so reactive and for this really cool solution Andrew!