topic Script: sum the values of a field in QlikView

Script: sum the values of a field

Sun, 11 Mar 2012 16:09:16 GMT

Hi,

Is there a way to sum the vlaues of a field in script? I need to use the total of all the foield values in the load statement fro a subsequent table. It would be nice if I could calulate the total of the field and assign this to a variable. I have tried the following, but it doesn't seem the work:

LET TotalPallets = SUM(PalletCount);

Where PalletCount of a previously loaded field.

Any suggestions?

Script: sum the values of a field

Oleg_Troyansky — Sun, 11 Mar 2012 16:27:14 GMT

You can use an aggregated load and then assign the value using function peek(). Something like this:

load

sum(PalletCount) as SumPalletCount

resident

MyTable

;

let vTotalPallets = peek('SumPalletCount');

cheers,

Oleg

Script: sum the values of a field

swuehl — Sun, 11 Mar 2012 16:31:40 GMT

I believe you need an additional load with an aggregation function SUM to get what you want.

Though the Help says about the aggregation functions:

These functions can only be used in field lists for Load statements with a group by clause.

It seems that you actually don't need a group by clause:

LOAD

sum(PalletCount) as SumPallets

resident YOUREXISTINGRESIDENTTABLE;

Let TotalPallets = fieldvalue('SumPallets',1);

This should result in a variable TotalPallets with your requested sum of PalletCount field values.

Hope this helps,

Stefan