Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
german_avanzato
Creator
Creator

Nested search or loop

Hi Experts,

I'm trying to determine the price of a product.

For this task, I need to summarize the cost of all his components.

Something like this:

Product                Component               Cost    

A                              aa11                        12

A                              bb32                         1

A                              cda                           43

B                              sdf                           13

B                              bb32                         1

Cost product A = 56

Cost product B = 14

But in some case, one or more of the component of the product, is another product:

Product                Component               Cost    

A                              aa11                        12

A                              bb32                         1

A                              cda                           43

A                              B

A                              C

C                              aa11                         12

C                              sdf                            13

C                              ccc                            4

B                              sdf                           13

B                              bb32                         1                   

B                               C                             

B                              hfsdf                         315

B                              hgf1                         45

Cost Product A = 488

Cost Product B = 403

Cost Product C = 29

So in this case, first I need to determinate the price of C, after that; the price of B, and finally the price of A.

Not always is the same sequence, but always can be solved.

I don´t know how to approach to this problem in QV.

Any suggestion?

Thanks

1 Reply
eddysanchez
Partner - Creator
Partner - Creator

Table:

LOAD * INLINE [

Product,                Component,               Cost   

A,                              aa11,                        12

A,                              bb32,                         1

A,                              cda,                           43

A,                              B,

A,                              C,

C,                              aa11,                         12

C,                              sdf,                            13

C,                              ccc,                            4

B,                              sdf,                           13

B,                              bb32,                         1                  

B,                               C,                            

B,                              hfsdf,                         315

B,                              hgf1,                         45

];

//Group

Left Join(Table)

LOAD Product as Component,

          Sum(Cost) as ComponentCost

Resident Table

Group By Product;

FinalTable:

LOAD Product,

          Component,

          If(Cost='',0,Cost) + If(IsNull(ComponentCost),0,ComponentCost) as Cost

Resident Table;

DROP Table Table;