Hi,
I have an item structure table with items and sub-items that I use in a HierarchyBelongsTo function to be able to compare Bill Of Materials and compare costs for subcontracting etc.
Say this is my table:
Item:
LOAD * INLINE [
NodeID, Name,ParentID,Qty
1a,BikeA,,
1b,BikeB,,
2a,FrontWheel,1a,1
2a,FrontWheel,1b,1
2b,RearWheelA,1a,1
2c,RearWheelB,1b,1
3a,Spokes,2a,10
3a,Spokes,2b,10
3a,Spokes,2c,10
]
;
Bom:
HierarchyBelongsTo (NodeID, ParentID, Name, AncestorID, AncestorName, Depth)
LOAD
NodeID,
ParentID,
Name,
Qty
RESIDENT Item;
Drop table Item;
Note how FrontWheel is a part of both BikeA and BikeB, and all wheels contain 10 spokes each. I then create a chart with Name and AncestorName as dimensions and a simple sum(Qty) as expression. My chart tells me that both my bikes have 2 Front and 2 Rear wheels each, and that the FrontWheel contains 20 spokes. And both of my bikes A and B have 40 spokes according to my chart. While I know that the wheels all contain 10 spokes and no Bike has more than 2 wheels.
Now is there a way to tell the expression to just follow a straight line up the hierarchy, and to “stop” at “wheels” if I select a wheel as AncestorID?
As this is just a simplified example a set analysis on depth level perhaps could have helped, but in real life there are many depth-levels and the customer might select AncestorID’s from many different levels and the “branching out” might have occurred deeper in the hierarchy.
Attaching my example.
Any help much appreciated!
