Script: sum the values of a field

[#1]

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?

[Oleg_Troyansky · Accepted solution]

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

[swuehl]

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