topic Re: Assistance with sum function in script: in QlikView

Assistance with sum function in script:

Thu, 31 Jul 2014 17:06:19 GMT

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.

Re: Assistance with sum function in script:

Anonymous — Thu, 31 Jul 2014 17:27:16 GMT

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 ....;

Re: Assistance with sum function in script:

maxgro — Thu, 31 Jul 2014 19:59:43 GMT

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

Re: Assistance with sum function in script:

Anonymous — Thu, 31 Jul 2014 23:24:54 GMT

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?

Re: Assistance with sum function in script:

maxgro — Fri, 01 Aug 2014 07:20:25 GMT

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