topic Re: sum with nested if in QlikView

sum with nested if

Wed, 25 Nov 2015 13:04:20 GMT

Hi ,

I have below script in my expression

when i have one row code like ;

Sum(if(((TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153)) ,Bedehkar-Bestankar)/1000000)

it's work fine But

when i have 2 row code like ;

Sum(if(((TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153)),
if(((TotalCode=86 AND PortID=3304) or (TotalCode=88 AND PortID=3304) or (TotalCode=87 AND PortID=3304)),Bedehkar-Bestankar)/1000000))

it's not work and receive me 0.

i want my script work with multi row code .

can anyone help me

regards

hamide

Re: sum with nested if

sunny_talwar — Wed, 25 Nov 2015 14:16:41 GMT

Can you share an example? It is hard to understand what your requirement is just by looking at the code

Re: sum with nested if

roger_stone — Wed, 25 Nov 2015 14:21:26 GMT

The second IF is tested if the first IF is true. I think you need to review your logic.

Re: sum with nested if

marcus_sommer — Wed, 25 Nov 2015 14:26:18 GMT

Try something like this:

Sum(match(TotalCode, 86,87,88) AND match(PortID, 1153,3304),[Bedehkar-Bestankar])/1000000))

- Marcus

Re: sum with nested if

Mark_Little — Wed, 25 Nov 2015 14:39:09 GMT

HI,

Yer more details would be helpful. even if you explain what you want in English?

It seems at the moment you are saying

If statement 1 is true,

Check that statement 2 is also true, then sum.

From look at that the logic it would be zero as your first statement returns values matching

(TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153)

So they can't then also match

(TotalCode=86 AND PortID=3304) or (TotalCode=88 AND PortID=3304) or (TotalCode=87 AND PortID=3304)

Maybe something like

Sum(if((TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153),

Bedehkar-Bestankar/1000000,

if((TotalCode=86 AND PortID=3304) or (TotalCode=88 AND PortID=3304) or (TotalCode=87 AND PortID=3304),

Bedehkar-Bestankar)/1000000))

Sum(if((TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153) or (TotalCode=86 AND PortID=3304) or (TotalCode=88 AND PortID=3304) or (TotalCode=87 AND PortID=3304),

Bedehkar-Bestankar/1000000)

Re: sum with nested if

buzzy996 — Wed, 25 Nov 2015 15:57:24 GMT

ur 2'nd if condition is wrong,

use Marcus_Sommer suggestion,tht helps.