Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
olac
Contributor III
Contributor III

HierarchyBelongsto and depths

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!

1 Solution

Accepted Solutions
olac
Contributor III
Contributor III
Author

To anyone else that stumbles upon the same problem, what I have found is that the best solution, by far, is to solve this already in SQL by using common table expression. I do not think the hierarchy or hierarchybelongsto has the power to work very well in a bill of materials situation.

View solution in original post

7 Replies
Gysbert_Wassenaar

Try adding an extra column in the BOM table. See attached example.


talk is cheap, supply exceeds demand
olac
Contributor III
Contributor III
Author

Yes this seems to be working very nice in my example. Will try it out in the real world on Monday.

Thanks a lot!

olac
Contributor III
Contributor III
Author

Hi again,

Unfortunately it did not quite do the trick. It looks better I must say but if I click on Ancestor 2a (the front wheel that belongs to both bikes) I still get a count of 20 spokes, while there is only 10 spokes in that wheel.

So close but not all they way. Any ideas to this?

Thanks

Gysbert_Wassenaar

Yes, well two bikes have that wheel, so there are two wheels with a total of 20 spokes. If you don't want to count that way either uniquely identify the wheel by picking the bike the wheel belongs to or use a sum(distinct Qty).


talk is cheap, supply exceeds demand
olac
Contributor III
Contributor III
Author

Thanks for your help with this! Could you explain what you mean by "uniquely identify the wheel by picking the bike the wheel belongs to"?

I am trying to achieve a chart where I get the count of material in the underlying structure of what I have chosen for AncestorID. Not include the count of whatever items my selection might be part of.

olac
Contributor III
Contributor III
Author

No one that has an answer to this?

olac
Contributor III
Contributor III
Author

To anyone else that stumbles upon the same problem, what I have found is that the best solution, by far, is to solve this already in SQL by using common table expression. I do not think the hierarchy or hierarchybelongsto has the power to work very well in a bill of materials situation.

View solution in original post