Multi-level Bill of Materials

[jonelis0523]

Hello all,

I am having a problem defining my BOM model. I have one field with ProductIDs and the second field is consisting of Materials that are included in the production of that product. Among the Materials there are sub-products that also are located between Products. I wish to have a table where I choose a Product and the table provides me with all consisting materials, hence expanding (and not displaying) sub-products.

I tried to apply Hierarchy but that it seems complicated for my case; also trying to make it work using Lookup but I got stuck.

I  chose to follow an approach where my script takes a value from Materials, checks whether this value is among ProductIDs  and:

1. if Material does not exist among Products - keep the value as it is a defined material
2. if Material exists - that means it is a sub-product and replace that as material and look for it again and add additional rows of materials - Iterate until no more values appear

Any help is welcome. 

[Or]

That looks like a classic case for Hierarchy - why do you think that's not the correct approach?

Since you need to do this iteratively, you'll have to either use the built-in hierarchy functions or iterate using a loop, which would pretty much just replicate the same behavior, I think.

[jonelis0523]

---

---

Maybe I do no understand how to use Hierarchy but what I have read in order to use Hierarchy as Qlik Sense built-in function: "The input table must be an adjacent nodes table. Adjacent nodes tables are tables where each record corresponds to a node and has a field that contains a reference to the parent node. In such a table the node is stored on one record only but the node can still have any number of children. The table may of course contain additional fields describing attributes for the nodes."

I do not have information what is what- I need to build it. I only have 2 fields and both - ProductID and Material - are string values.

[Or]

In that scenario, Material is your node and ProductID is your parent, isn't it? Perhaps I am not understanding the structure correctly...

[MarcoWedel]

please post some sample data and your expected result

[jonelis0523 · Accepted solution]

I am investigating the result now but it looks I got what I needed by using Hierarchy function - Product is my NodeID and Material - ParentID (seems weird), and the third - NodeName - extrapolates to multi-level. Important: I duplicated my Material field for NodeName:

Hierarchy (ProductID, MaterialID, NodeName)
Load

ProductID ,
MaterialID,
MaterialID as NodeName

Thanks for the guidance!

[jonelis0523]

@MarcoWedel thank you for the message.

Please find attached my sample data and wished result both as xlsx.