Component and Sub Components

[andrea90casa]

Hello Community,

I would like to ask you for some help in order to understand if it's possible to solve the following problem using Qlik Sense script code:

I have a table with Components, and Sub Components (stuffs that you need in order to create Components), like this one:

Component      SubComponent      Quantity

ProductA 

ProductB

X                           ProductA                        2

Y                           ProductA                        1

Z                           ProductA                        3

I tried to use a Self-Join (Inner Join) clause in order to create a table like that:

Component      SubComponent      Quantity

ProductA              X                                      2

ProductA              Y                                      1

ProductA              Z                                      3

And I did it using this code:

Test:
LOAD
    Component,
    SubComponent,
    Quantity
FROM [lib://Self/SelfJoin.xlsx]
(ooxml, embedded labels, table is Tempt);
;

INNER JOIN

LOAD
    Component             as Product,
    SubComponent     as SubProduct,
    Quantity                    as Qtà.
RESIDENT
 Test
;

Then, i tried to complicate the stuff:

I added two rows at the original table:

Component      SubComponent      Quantity

ProductA 

ProductB

X                           ProductA                        2

Y                           ProductA                        1

Z                           ProductA                        3

ProductC          X                                         4

ProductE          X                                         3

Now i would like to do the same thing in order to have this table:

Component      SubComponent      Quantity

ProductA              X                                      2

ProductA              Y                                      1

ProductA              Z                                      3

ProductA            ProductC                       8

ProductA            ProductE                       6

For example, in order to have 1 ProductA I need 2 "X" SubComponent, and for 1 "X" I need 4 ProductC, with a total of 8 units of that. And so on.

I would like to ask you if there are any ways in order to reach that goal.

Thanks in advance,

Andrea

[Vegar]

You could take a look at the Hierarchy function for trying to solve your problem.  I'm not sure if you get that flattended table straigt out of the function, but it could absolutely help you solving multiple layers of product relations. 

[petter]

Agree with Vegar - and I believe this article that Henric Cronström wrote can be adapted to your purpose:

https://community.qlik.com/t5/Qlik-Design-Blog/Bill-of-Materials/ba-p/1462792

[andrea90casa]

Hello Petter,

I tried using Hierarchy, but it doesn't work as I expected, I mean if I have this very simple table:

BOM:

Hierarchy(NodeID,ParentID,Name)

LOAD * Inline [
    NodeID,ParentID,Name,Quantity
    A,,PROD_01, 1
    B,A,Comp_01, 1
    C,A,Comp_02, 2
    D,C,Comp_03, 2
]
;

I would like to receive a table like this one:

PROD_01     Comp_01    1

PROD_01     Comp_02    2

PROD_01     Comp_03    4

And everything could be fine, except for that "4" that I didn't understand how to calculate it, because I always receive 2 as  output.

Kind Regards,

Andrea

[Vegar]

I see your issue, but I'm sorry that I don't see the solution straight away.  You could try to investigate the alternative structured table method called HiearchyBelongsTo.

Good luck.