Assistance with sum function in script:

[#1]

I have the following fields:

Load number

InvTotal

Duty

MPF

HMF

DocTurnOver

I want to write a new column in my table that is

InvTotal minus the following:

Duty

MPF

HMF

DocTurnOver

I have tried it several different ways with out anything working.

Not sure if it matters, but

InvTotal has value for ALL Load numbers

Rest of the fields may be blank.

[Anonymous]

Load

InvTotal,

Duty,

MPF,

HMF,

DocTurnOver,

InvTotal - (Duty  + MPF + HMF + DocTurnOver) as Calculation

from ....;

one thing to consider is if you have null values in one of the fields, the calculation will be null as null propagates in Qlikview. so if that is the case, then you will have to either set the values to zero or skip the null values...something like below:

option 1:

load *,

InvTotal - (Duty  + MPF + HMF + DocTurnOver) as Calculation;

Load

alt(InvTotal, 0) as InvTotal,

alt(Duty, 0) as Duty,

alt(MPF, 0) as MPF,

alt(HMF, 0) as HMF,

alt(DocTurnOver, 0) as DocTurnOver

from ....;

option 2:

load *,

If(isnull(InvTotal) or isnull(Duty) or isnull(MPF) or isnull(HMF) or isnull(DocTurnOver), 0, InvTotal - (Duty  + MPF + HMF + DocTurnOver)) as Calculation

;

Load

InvTotal,

Duty,

MPF,

HMF,

DocTurnOver

from ....;

[maxgro]

load

     *,

     InvTotal - rangesum(Duty, MPF, HMF, DocTurnOver) as newfield

     ....

from online help

rangesum(expr1 [ , expr2, ... exprN ])

Returns the sum of a range of 1 to N arguments. As opposed to the + operator, rangesum will treat all non-numeric values as 0.

Examples:

rangesum (1,2,4) returns 7

rangesum (1,'xyz') returns 1

rangesum (null( )) returns 0

[Anonymous]

Simple and elegant solution Massimo.

Still need to check if InvTotal is null or not, right? Why not include it within rangesum and assign negative values to other fields?

[maxgro · Accepted solution]

you can use alt, len(trim(...))>0, isnull()

or may be (try, I think it should work)

rangesum(InvTotal, -Duty, -MPF, -HMF, -DocTurnOver) as newfield