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

[dengzhekai]

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)

[Qiu]

@dengzhekai 
What will be your sample input and output?

[dengzhekai]

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

[Qiu]

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

[dengzhekai]

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!

[oly]

Hi @dengzhekai ,

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

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740

Hope it helps, happy solving,

Oly