re-structure BOM hierarchy parent/child

[christian_kopp]

Hi,

I have a BOM structure which can have different levels based on the complexity of a product.
The levels are shown in columns 1-5 but can be also more or less.
In column “material” I have the material number and in column “level” I have already the BOM level.

I need a new column which gives me the parent of the material number.
For example material A7 is A3.
I could not find a solution to have it flexible since the number of levels can change.

Thank you very much for any help!

[takbb]

Hi @christian_kopp , can you upload that sample spreadsheet and then it will be easier for people to “have a play” and give you some help

Also, are the parents really all going to have the same name? I suspect not, so it would make sense for the sample data to be more representative, or else it may be challenging to prove the solution works

[christian_kopp]

KNIME_BOM.xlsx (3.5 KB)
here’s the example file

[christian_kopp]

Hi @takbb , thank you for the hint, I uploaded the example

[christian_kopp]

It can happen that the description for some SEMI is the same.
But the identifier is the material, so the parent should not be the name/description, but also the material number

[takbb]

thanks @christian_kopp and my apologies for my misunderstanding earlier.

The attached will do what you are asking I think, for the specific sample. It relies on the column names for the hierarchy having a number as the suffix that identifies the level in the hierarchy. e.g. they are currently called column1, column2 and so on. They could be called something different e.g. hier1, hier2 and the nodes would then need to be updated to take this new name into account, but the important thing is the still have 1,2,… on the end.

It first uses string manipulation across all of the “columnN” columns to place the “material” for that level at the position in the hierarchy where there is currently an entry (e.g. FIN, SEMI), using a conditional expression which takes the form:

CONDITION ? ValueIfTrue : ValueIfFalse and returns it as a string, as follows:

string(
length($$CURRENTCOLUMN$$)>0
?$material$
:toNull("")
)

It then uses Missing Value to fill down these “parents” so that it builds the hierarchy for each row

Finally the Column Expressions node is used to determine the parent for the material on each level by referencing the column name that ends with (level - 1).

For completeness, it then joins back to apply the parent column to the original table, using material as the join key

I hope that gives at least one idea. You will doubtless need to modify it to apply to your real table if column names are different.

Determine BOM parent.knwf (19.8 KB)

[christian_kopp]

@takbb

to determine the parent for the material on each leve

Awesome, works like a charm.
Thank you very much or the quick help.