Working with Hierarchies

[matthewjbryant]

Hi All,

Have any of you ever worked with hierarchical data in Qilkview? I've written a SQL statement that shows me all our parts and how they are linked to their component parts. Level 1 parts are used to make a level 2; level 2 to make level 3; and so on.

My problem now is linking these hierarchies together. As you can see in the example below it is easy to work with the first 3 lines, but on line 4 I have a part that is liked to the first line, and for part g I have a part that is linked to parts d and a. How can I find the hierarchical products of all the Sales Parts' quantities (i.e. for part g I need to multiply 3x6x2).

Example Data:

Level	Part	Qty Used	Sales
1	a	2	Y
2	b	1	N
3	c	5	Y
2	d	6	Y
3	e	5	N
4	f	2	Y
3	g	3	Y
1	h	6	N
2	i	3	Y

Required result:

Sales Part	Qty Product
a	2
c	10
d	60
f	120
g	36
i	18