Successive / iterative row calculation

[Zapata_123]

Dear community,

I just switched from Excel to Alteryx and would like to solve the following problem:

-I have a bill of materials of a car (i.e., a list of all components used in a car and the car part they belong to)

-I want to calculate the full quantity of each line, which requires a successive calculation, as the quantity depends on the parent part

Example: 

-In the picture and excel attached, one car consists of one powertrain, one body, one electrical supply system and one interior

-The Body consists of 1 hood, 4 doors and 1x Windows

-Each door consists of one handle, one lock and one seal

-Hence, the quantity shown in the BOM is 4 for "Doors" , but only one for "Handle", "Lock" and "Seal"

-So, to calculate the number of, e.g., Handles used, I need to multiply the Handle quantity (1) with the full quantity of the parent part (Doors, 4).and the quantity of the Body (1)

In Excel, this is easy to implement using two separate columns:

-Column "Full quantity": = IF (No parent part available) THEN [quantity shown in BOM] ELSE [Full parent quantity] * [quantity shown in BOM]

-Column "Full parent quantity"=LOOKUP([Full quantity] of parent part)

Note that the two formulas are cross-referencing. This does not pose a problem in excel as rows are calculated successively. Do you have an idea how I can implement this in Alteryx?

The example shown here is simplified. The real data consists of >100,000 lines, hence solutions should ideally be computationally stable.

Any suggestions are welcome :-)

[MarqueeCrew]

Hello @Zapata_123,

I'm sorry but I don't have a suggestion.  I do however have a potential solution.  I used your explanation of the Excel process to create the same logic in Alteryx.  I created a filter to impose the first IF-THEN-ELSE statement and then calculated Parent Qty.  Then I performed a lookup to calculate Full Parent Qty and the joined that result back to the ELSE records.  Then I calculated the Full Parent Quantity using the expression given.  Finally I used a UNION to bring all of the rows back together.  For ease of comparison, I applied a SORT.  Since n/a is not a number, i allowed my null() result to match to your desired n/a.

I hope that this helps solve the problem for you.

Cheers,

Mark

[Zapata_123]

Hi Mark,

thanks for the idea! It works well for the example provided. However, if one of the parent quantities is >1, it does not work anymore. For example, if we set the quantity for "Body" to two, the total quantity for handle, lock and seal should be 8 (see updated excel attached). I am aware that this example does not make sense as there is only one car "Body", but in the real data there are many examples where a similar case occurs.

Do you have any idea how to adapt the workflow so that is valid in this case as well?

Kind regards,

Stefan

[Joe_Mako · Accepted solution]

How about the attached workflow and Iterative Macro:

- Select to create the before situation and set the data types

- Formula to add the fields "Full Quantity" and "Parent Quantity"

     - Macro - Join with self on Parent ID to ID

     - Left out is the results, records without a parent

     - Formula to calculate Full Quantity, and that output will be looped back in

- Sort for comparison (not necessary)

[Zapata_123]

Mike,

thanks for the macro, that is brilliant! It works well and I was able to use a similar approach for some related questions that I had :-)

Thanks a ton!