conditional sum

[#1]

hi all,

I have a table in which I have a field called "total_duration" which is going to be summed ..and I have another field called "operator_code" and in that operator_code can be any of 3 digits but they also have values like "997", "998" "999" ..i need to sum only those row whoz operator_code falls in "997" OR "998" OR "999"

so can i use multiple OR's here?

like

sum(if operator_code="997" or operator_code="998" or operator_code="999", call_duration,0)

is this the right way or what? plz guide.

[Anonymous · Accepted solution]

Nothing is logically wrong here, except the synthax. It is
sum(if (operator_code=997 or operator_code=998 or operator_code=999, call_duration)
Simpler will be
sum(if(operator_code>=997 and operator_code<=999, call_duration)
Even better to assiciate some flag in the script for these values:
LOAD
...
operator_code,
if(operator_code>=997 and operator_code<=999, 1, 0) as Flag
...
and in calculations you can simply use
sum(call_duration) * Flag)

[johnw]

If we're sticking with expressions, slightly simpler still (at least for a list this short) is the match() function:

sum(if(match(operator_code,997,998,999),call_duration))

But if this is in version 8.5, even better is set analysis:

sum({<operator_code={997,998,999}>}call_duration)

Set analysis will execute significantly faster than an if(). If you're doing the same thing in several places, flagging them in the script as Michael suggested is probably better still. Or you could standardize on always flagging for this sort of problem so that you don't have two different approaches, or have to switch approaches when you add more charts. I've also been known to do the following in my script:

LOAD
...
,operator_code
,if(match(operator_code,997,998,999),operator_code) as operator_code_special

Then you can use operator_code_special as a dimension, suppress when value is null, and just use sum(call_duration) as your expression. So lots of options, as is often the case in QlikView.