topic Re: Nested search or loop in QlikView

Nested search or loop

german_avanzato — Thu, 18 Apr 2013 14:06:09 GMT

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

Re: Nested search or loop

eddysanchez — Thu, 18 Apr 2013 14:41:02 GMT

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;

Re: Nested search or loop

german_avanzato — Thu, 18 Apr 2013 15:43:53 GMT

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

Gysbert_Wassenaar — Thu, 18 Apr 2013 17:21:03 GMT

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

Re: Nested search or loop

german_avanzato — Thu, 18 Apr 2013 17:28:23 GMT

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

Thanks.