Sum and Group By in Load Script in QVD vs QVW

[#1]

Hello,

I am summing values in the load script of my QVW's, and want to move this summation to the QVD level instead. However, when I add the same script to the QVD, the summations are multiples of what they should be. When I add the exact script to the QVW, the summations work properly. Is there a trick to get these to work at the QVD level as well? Here is my partial script:

SummedTotals:

Load

  ProjectItemID,

  sum(Distinct [Item Sell Price Total]) as SummedItemSellPriceTotal

Resident Orders

Group By ProjectItemID;

From the QVW,

Sum(SummedItemSellPriceTotal) = $2,022.19 for one item.

For this same item, the QVD gives:

sum(SummedItemSellPriceTotal) = $6,066.57

Why is this happening?

Thanks!

[sunny_talwar]

Not sure I understand. You are summing the Sum? and what do you mean when you say from the QVW and from the QVD?

[Mark_Little]

Hi,

Could you give some more details.

When you store the above to QVD is it as it own QVD or is it joined back to the order QVD?

If you use the Function MAX(SummedItemSellPriceTotal) when pulling through from QVD do you get the right value?

Mark

[#4]

Each item can have multiple line items depending on when something is fulfilled. For instance, a customer could order 5 items. 2 are in stock and ship immediately. 3 are ordered directly from the manufacturer. In the load script, we are summing these to get a total for each line item. Then, in the expression, we are summing each item to get totals for date ranges, orders, divisions, etc.

When we have this script in the application we are building (QVW) it works. However, we want to move it to the QVD level. Taking the same logic to the QVD gives multiples when we sum it in the expression.

[#5]

I have done a little more digging and discovered that the difference is coming from the Join used in the QVD which I was not using in the QVW. The sum works properly when it is not joined back to the original table. However, to save everything as one QVD, it must be joined back into the main table. I have tried a left join, inner join, and outer join, all of which give inflated results. However, using no join works. Is there any reason the joins would be changing the values?

[swuehl]

Any reason why you do a SUM( DISTINCT ...) in your SummedTotals script snippet?

Maybe you can detail a bit more how your Orders table look like and why you want to join the Total sum back to the original table at all?

[Mark_Little]

HI, 

Like Swuehl said some more details would be useful and I think I know what he is getting at. You are using sum(Distinct...) so it is probably the case that if you just sum() you would have the duplication before the Join.

One option would be to maybe have these values concatenated on the the ordered table, But on the original table add a line

'Line'          as Type

then in your grouped table

'SummedItemSellPriceTotal'         as Type

The values will still be duplicated, but you can pick out the values you want.

SUM({<Type={'SummedItemSellPriceTotal' } SummedItemSellPriceTotal>})

I theory you wouldn't need the Type={'SummedItemSellPriceTotal' }  as the lines would have a value in the SummedItemSellPriceTotal field, but it allows you to separate the lines from the totals.

Hope this Helps

Mark

[#8]

Thank you for the suggestions. I will try adding a Type into the table.

To give a little more details about the data structure, we have an orders table that includes an ProjectID and order level information. This then joins to a products table through the ProjectID. The products table has a ProjectItemID for each line item in the order. We calculate [Item Sell Price Total] for each ProjectItemID. We initially had this in the expressions of the charts as sum(aggr(sum(distinct [Item Sell Price Total]), ProjectItemID)) to get the sum of the totals without double counting. Without the aggr(), this would double count. However, this expression requires a lot of computing power, which led us to add the script posted above to our load script. This worked in our applications, but because it is required in 99% of the qlikview applications we build, we wanted to move this to the QVD level in order to calculate it only once and then reference it as a field in our applications. To add it to the QVD, we have to join it to the main table since QVD's can only save one table. However, when we add the join, this begins to double count again, whereas without the join, it works.

Hopefully this provides the additional information you need.

Thank you for your help!