Hierarchy BOM

[noahfels]

I have a BOM which look like this:

BOM:
Load * Inline [
Item_Parent, Item_Child, Amount
29M20, Z16, 1
29M20, Z17, 1
29M20, L00, 1
L00, R00, 2
R00, K10, 1
R00, K20, 1
R00, K30, 1
R00, K40, 1
20M20, L00, 1
];

My goal is to calculate the quantity needed for each item below the one I select.

E.g. if I select 29M20 then I want to know that I need 1xZ16, 1xZ17, 1xL00, 2xR00, 2xK10, 2xK20, 2xK30, 2xK40.

I have tried using hierarchy loads but I don't get the desired result, perhaps I am using it wrong.

[abhijitnalekar]

Hi @noahfels ,

Please try the below Expression.

=aggr(concat(Amount&'X'& Item_Child,',') ,Item_Parent)

[noahfels]

In that case I only get the amount of the direct children, but I need to get the amounts and the link to all children_items below a parent_item.

[abhijitnalekar]

Hi @noahfels ,

My bad, I was not able to get the requirement correct in the initial reply.

Please create hierarchy like below. Hope this will solves your purpose.

[noahfels]

Thanks for your reply, but this still doesn't solve the issue. If I select 29M20 now, I still do not get any information that this item includes 2 x R00 and therefore 2 x K10, K20, K30 and K40.

[abhijitnalekar]

Hi @noahfels ,

I am afraid that I am not able to understand the requirement properly.

As per the inline-table, you have given the parent 29M20 have 1XL00,1XZ16,1XZ17. Please correct me if I am wrong.

Can you please share any diagram or table how you want the output

[noahfels]

Correct  @abhijitnalekar , but since one 29M20 includes one L00, therefore it also includes all child_items of L00 (i.e. 2 x R00). And R00 itself has four child_items (i.e. K10, K20, K30, K40). Therefore 29M20 includes 2 x R00 and 2 x (K10, K20, K30, K40). It is a bill of materials structur which is build like a hierarchy (tree). I think some form of a hierarchy load is needed.

[Saravanan_Desingh · Accepted solution]

I think, your expected output is wrong.

For 29M20, you should get - 1xZ16, 1xZ17, 1xL00, 2xR00, 1xK10, 1xK20, 1xK30, 1xK40.

Try this,

BOM:
HierarchyBelongsTo(Item_Child, Item_Parent, Amount, ParentName, Node, Depth)
LOAD *, Item_Child As NodeName;
Load * Inline [
Item_Parent, Item_Child, Amount
Root, 29M20
29M20, Z16, 1
29M20, Z17, 1
29M20, L00, 1
L00, R00, 2
R00, K10, 1
R00, K20, 1
R00, K30, 1
R00, K40, 1
20M20, L00, 1
Root, 20M20
];

Expressions:

=Concat(Amount&'x'&NodeName, ', ')
=Sum(Amount)

[noahfels]

This seems to be kind of what I am looking for, but I would have to concatenate an entry for each root item once, correct?

[Saravanan_Desingh]

Yes. But the original Table wont be there after

HierarchyBelongsTo

So, you have to use Depth & Node combination to identify the Root.