Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Need help on setting up an iterative marco to do a "for loop" calculation

5 - Atom

Hi All,


I am pretty new using alteryx, and need some help setting up an iterative macro to some some calculation.


What I want to achieve:

I have a set of product mfg steps (from Product A to E), and I know the conversion factor only between the intermediates. I want to calculate the composition of materials via following the product mfg. steps like the following picture.


For example, given I need to have 100 Product A, I need to make 100 Product B, 200 Product C, and 10,000 Product E, and 200 Product D, and get the conversion factor between finished product and its ingredient.


I have attached the sample data set for the screenshot I shared. 


From the things I read, I think I can achieve this via iterative marco. But I haven't had luck.... :( 




Here is my thought on the "algorithm" / for loop:


  • For each component in column C
    • Set up a dummy variable "temp" = 1 
    • Search whether or not Column B is equal to Product A (finished product),
      • Yes, the conversation factor equals to current conversion factor * Temp 
      • No, update the temp with temp = temp * current conversion factor
        • Search the previous step component from Column C to Column B until it can equal to Product A, and update the temp

I think the visual I provided above with sample data would better illustrate my thought. But basically, for each product, I want to search for its "child" and see if it equals to the finished product. If so, I stop. If not, I record the conversion factor, and keep searching until I reach the finished product (Product A)


20 - Arcturus
20 - Arcturus

What will be your sample input and output?

5 - Atom

I have the sample input in the original post and attached my expected output in below

20 - Arcturus
20 - Arcturus

I think a larger dataset might be helpful to validate the flow.


5 - Atom

Hi Qiu @Qiu 


This is very helpful! I have created slightly more complicated examples that are more representative ( see below for this product flow)





I think the solution you had for my earlier example works because the conversion between B and A is 1 so only "one" calculation is needed. 


I attached a more complicated data set. This is why I need I probably need a batch macro (for each finished product) and iterative macro (to calculate the final conversion rate between the finished product and its ingredients)


Thanks for helping me with this! This is very helpful!

Alteryx Alumni (Retired)

Hi @dengzhekai ,

there's a classical management hierarchy challenge and the Macro is published as solution. Should be very easy to adapt and use!

Hope it helps, happy solving,