topic Re: Nested IF while, ignore negative values with exception in QlikView

Nested IF while, ignore negative values with exception

Anonymous — Tue, 05 Jun 2012 12:27:40 GMT

Hello everyone,

I have a special case/user requirement that in a pivot table I would show a SUM, ignore negative values from the source table BUT make one exception.

I believe a nested IF should do the trick, but all my tries so far failed; I got a bunch of different results... except of course, the one I'm looking for :-).

It looks like my syntax is correct, but my logic is wrong; I was wondering if the Community may be able to point me in the right direction.

SOURCE TABLE 1

ID	Value1
1	10
2	20
3	1

SOURCE TABLE 2

ID	Value 2
1	10
2	-10
3	-20

My Pivot Table is simple:

Dimmension: ID

Expression: Sum(Value1 * Value2)

The desired Pivot Table would look like this:

Header 1	Header 2
1	100
2	0
3	-20

Basically:

1. IF(Sum(Value1*Value2)<=0,0,Sum(Value1*Value2))

2. IF(Table2.ID = '3', Sum(Value1*Value2))

How do I combine the two IFs so that it will ignore (0) all negative values in SOURCE TABLE 2 except where ID=3?

Thank you very much in advance

Re: Nested IF while, ignore negative values with exception

Tue, 05 Jun 2012 12:43:17 GMT

I think that this expression will help...

IF(ID = 3, Sum(Value1*[Value 2] ),
 IF(Sum(Value1*[Value 2])<=0,0,Sum(Value1*[Value 2]))
)

See attached file for the sample.qvw that I made for this issue

Re: Nested IF while, ignore negative values with exception

Anonymous — Tue, 05 Jun 2012 19:33:14 GMT

Thank you very much Roberto for your quick reply; I have tried your expression and it works to some degree.

What I mean by that is the numbers in the Combined column are OK, but when I do a Total for the ID dimenssion, the result is 0, when it should be 80.

Either I'm missing something, or this is kinda strange behaviour from QV.

Re: Nested IF while, ignore negative values with exception

Tue, 05 Jun 2012 23:22:19 GMT

You may want to try moving your sum aggregation outside of the IF statement.

COMBINED Column

sum(IF(ID = 3, Value1*[Value 2],

if(Value1*[Value 2]<=0

,Value1*[Value 2]

)

I've attached Roberto's sample with the modified formulas.

Re: Nested IF while, ignore negative values with exception

jemancilla — Wed, 06 Jun 2012 03:16:02 GMT

If you want that it ignores only the negative values in TABLE 2, except when ID=3, then .......

Sum(If(ID=3,Value2*Value1,If(Value2<=0,0,Value2*Value1)))

Re: Nested IF while, ignore negative values with exception

Wed, 06 Jun 2012 05:26:23 GMT

Ah, I didn't notice the incorrect total. Datawitch indeed seems to have the corrected version for you 🙂

Re: Nested IF while, ignore negative values with exception

Anonymous — Wed, 06 Jun 2012 12:12:00 GMT

Absolutely great stuff; Datawitch expression does give me the result I want. I struggled with this for a few days and now I look at the solution and it seems so simple! Not a good feeling for me :-).

Thank you very much everyone for the help!

PS Apologies to Datawitch as I can only have one correct answer.

Re: Nested IF while, ignore negative values with exception

Wed, 06 Jun 2012 16:11:50 GMT

No worries, I'm glad I was able to help. I ran into that a few months ago myself.