QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (5)
4 Replies
Contributor

Re: Nested search or loop

Table:

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)

Sum(Cost) as ComponentCost

Resident Table

Group By Product;

FinalTable:

Component,

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

Resident Table;

DROP Table Table;

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
Contributor

Re: Nested search or loop

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

Thanks.