Iterating over a multi-level Bill of Material

[rkehrli]

Hello

I’m still fairly new to KNIME and I’m a bit stuck building a loop to generate a multi-level Bill of Material from our E1 database.

I’ve got a simple recursive loop that works fairly well but it produces a flat unordered BOM table:

Here’s what I would like to get:

The first input into my “BOM reader” is 400350012 which returns the following table:

Another issue is that I don’t know beforehand how deep the BOM is, we have some that are 10 levels deep.

The idea is also that I can input an arbitrary amount of parent items (works by wrapping a Group Loop around the whole recursive loop).

Thanks

[takbb]

Hi @rkehrli , you’re certainly on the right lines using the recursive loop for this.

I know your data is in a database, which is always a little more difficult to assist with her on the forum, but could you perhaps output a small sample of data to a file (with sensitive information removed) and upload here, along with an idea of your expected output for that sample data.

Typically with the recursive loop, you will keep iterative until you have exhausted the data, which I guess is what happens in your workflow when the joiner returns no further rows, so not knowing the number of levels should be fine.

[takbb]

@rkehrli , I have uploaded a demonstration of a method of processing a BOM structure using a recursive loop.

Bill of Materials (BOM) with recursive loop demonstration – takbb

You will see at the top of this flow is an annotated set of nodes for creating the BOM structure and product list which are included in the flow. You can edit the Table Creator to modify the hierarchy if you wish to experiment, and then re-execute the upper flow to build the new csv files which will then be used in the lower flow.

In the lower flow, select the “Choose Item and Quantity” component and press F10 to open the view and choose the item to be “built”.

Then execute the flow and the recursive loop will iterate the BOM and find a list of all the products and components required.

I don’t know what output you are wanting, and obviously this is just a general purpose example, but maybe it will give you some clues?

Examples:

Parts List:

Bill of Materials for “building” 2 Tables

Bill of Materials for “building” 1 Dining Suite

[rkehrli]

@takbb
I’ll try to take a look at your supplied workflow this afternoon, I’m sure I can draw some conclusions for my “issue” from it.

I’ll also supply a sample table with actual data from our database.

[rkehrli]

Just missed the editing window by a could of hours it seems.

This is a full, multi-level bill of materials from our database:
Actual_BOM.xlsx (11.0 KB)

The reason why I’m trying to rebuild it in KNIME is because this BOM is actually built “on-demand” and there is no way to export it unless you do it by hand.

There is a “flat” BOM that gets generated once a day that is pretty much identical to the one I’ve made but we sometimes need an ad-hoc BOM if we have a new parent item or we need a multi-level one so we can do some calculations based on the parent items.

This is what my recursive loop is outputting:
Example_BOM.xlsx (10.3 KB)

Note: the column IXKITL normally contains the parent item short number, I’m ommiting that in the joiner as I don’t need it in the export but it might be useful for the data handling.

Example_BOM_Concatenate.xlsx (4.3 KB)
Example with just Concatenate and Duplicate Filter

[takbb]

Hi @rkehrli , assuming my understanding of your structure in Actual_BOM.xlsx is correct, the workflow can be adapted to iterate over your structure.

Actual-BOM	Workflow-BOM
Parent Item	Part ID
2nd Item Number	Component Part ID
Description	Component
Quantity	Quantity

In the workflow structure, I have also given the (repeated) description for the “Parent” in each case, and this can be derived by a self-join to transform your data into a form that will work with the workflow. (Annotated in the orange box)

e.g.

With that done, the remainder of the workflow can work with minor modification - your part IDs are numeric, and so I have converted them when they are read to String (also annotated in Orange boxes)

Choosing to build a particular item results in the BOM with items repeated according to the required quantities in the source BOM Structure.

Here is the workflow containing your adapted data

Bill of Materials (BOM) with recursive loop demonstration - adapted.knwf (169.9 KB)