Getting all sub-products associated with main products using recursion

[scoetzer]

Hi there Alteryx community,

I hope you can help me: I have a multi-level Bill of Materials, with the main products, components, sub-components, sub-sub-components etc. listed. There can be many 'levels' in the chain of the product bill of material. Here is an example dataset: 

Product	Material
Product 1	Component 1
Product 1	Component 2
Product 1	Component 3
Component 1	Subcomponent 1
Component 1	Subcomponent 2
Subcomponent 2	Subsubcomponent 3
Product 2	Component 4
Subsubcomponent 3	Subsubsubcomponent 4

I want to transform this table into a table where I can see for each main product (defined as: products at the beginning of the 'chain') what are all of the materials for each product. The output for this example should look something like this: 

Product	Material
Product 1	Component 1
Product 1	Subcomponent 1
Product 1	Subcomponent 2
Product 1	Subsubcomponent 3
Product 1	Subsubsubcomponent 4
Product 1	Component 2
Product 1	Component 3
Product 2	Component 4

 I suspect that potentially recursion would need to be used (as for each product we don't know up front how many 'levels' it would have), but I'm really banging my head trying to figure out how to achieve this.

Hoping that there will be some good suggestions from the community :) 

[Luke_C]

Hi @scoetzer 

This weekly challenge might give you some ideas: https://community.alteryx.com/t5/Weekly-Challenge/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740

It has a similar hierarchy concept.