Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
Sounds like a BOM (Bill of Materials) problem. See here for an example of how to deal with that.
Yes, it's something like that. I will see if this resolve my problem.
Thanks.