Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
andrea90casa
Creator
Creator

Component and Sub Components

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

Labels (1)
4 Replies
Vegar
MVP
MVP

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. 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
petter
Partner
Partner

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
Creator
Creator
Author

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
MVP
MVP

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes