Apply a ratio across two rows of data, qvw attached

[mellerbeck]

So given

     Product,      Source Location,      Processed Location,      Weight,      Produced

         AA,           4,                               1,                               1500,                50

         AA,           4,                               2,                                 500,                50

The produced 50 is the grand total

So, need to calculate what was actually produced using the weight as a ratio.

So for the first line (location 1) since it produced 1500 weight of the total 2000 weighted produced would be 37.5

and for the second line (location 2) weighted produced would be 12.5

so the end result would be

  Product,      Source Location,      Processed Location,      Weight,      Produced,     Weighted Produced

         AA,           4,                               1,                               1500,                50,          37.5

         AA,           4,                               2,                                 500,                50,          12.5

I think you can aggregate it during the script load using a group by of some sort? Can't quite wrap my brain around it

thanks for any tips!

[maxgro]

sum(Produced)* sum(Weight) / sum(total Weight)

[mellerbeck]

That's sort of close but, if there are other rows of data say,

BB, 4, 1, 100, 1

CC, 4, 2, 200, 2

It needs to only apply the ratio across the matching Product

[#4]

Hi,

I dont have access to Qv at this moment but in general would it not be possible to do it in two group by steps?

1. First a group by Product wher you calculate the full amount of weight for the Product
2. Do a resident load and sum of weight vs the total amount in step 1 and wrap a group by Weight and product around that? 

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

hope this helps

regards

Marco

[jagan]

Hi,

In that case then specify Product in below expression with Total like below

sum(Produced)* sum(Weight) / sum(total <Product> Weight)

Hope this helps you.

Regards,

Jagan.

[#7]

Hi Michael,

Have a look at this solution. Might help you get it to work as you like if you plan to run it in the backend.

Good luck!