Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

german_avanzato
Contributor

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

4 Replies
eddysanchez
Contributor

Re: Nested search or loop

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;

german_avanzato
Contributor

Re: Nested search or loop

This is useful but not quite right.

I think in that solution, but the problem is the result ist wrong:

Product Costo
A 459
B 403
C 29

Because when you try to determinate the cost of A, the cost of B is 374 because the C is not yet summarize. Its like you need to repeat this step until all Product that are Components has his cost.

Re: Nested search or loop

Sounds like a BOM (Bill of Materials) problem. See here for an example of how to deal with that.


talk is cheap, supply exceeds demand
german_avanzato
Contributor

Re: Nested search or loop

Yes, it's something like that. I will see if this resolve my problem.

Thanks.

Community Browser