topic Re: Using if condition in an expression in QlikView

Using if condition in an expression

Tue, 03 Jun 2014 10:43:46 GMT

Hi, I am new to Qlikview and when I try to use the if statement, it keeps bringing the value zero. Can anyone help with the below please.

I need the expression to count Organisation names (ignoring duplicates) where the AUM is greater than zero. This is what I wrote in the expression -

= if(new_currentaum>=0, count(distinct [Organisation Name]), 0)

Thanks

Jalps

Re: Using if condition in an expression

MayilVahanan — Tue, 03 Jun 2014 10:46:34 GMT

Try like this

count({<new_currentaum = {">=0"}>}distinct [Organisation Name])

Re: Using if condition in an expression

Tue, 03 Jun 2014 10:46:49 GMT

try:

= count(distinct if(new_currentaum>=0, [Organisation Name]))

Re: Using if condition in an expression

Tue, 03 Jun 2014 10:50:28 GMT

That works! So I dont need to use if statement in Qlikview it seems. Thanks very much

Re: Using if condition in an expression

MayilVahanan — Tue, 03 Jun 2014 10:52:15 GMT

You can use if statement, but set analysis produces the result faster

Re: Using if condition in an expression

Tue, 03 Jun 2014 10:54:57 GMT

Jalpa,

in your case set analysis proposed by Mayil Vahanan Ramasamy is better

Of course there are also cases, where if is useful ....

regards

Darek

Re: Using if condition in an expression

Tue, 03 Jun 2014 11:07:04 GMT

Thanks again. Is it also possible to use set analysis or if statement on another expression rather than a dimension? For example, in the above condition I was trying to put the condition that if AUM is greater than zero then count Organisation Name. However, I now have an expression which subtotals the AUM. Can I use that expression in the if statement?

Re: Using if condition in an expression

hic — Tue, 03 Jun 2014 11:18:49 GMT

Your expression cannot be evaluated properly. The reason is that you have a field reference that isn't aggregated.

= if(new_currentaum>=0, count(distinct [Organisation Name]), 0)

QlikView evaluates this expression once for every dimensional value, so if there are several new_currentaum possible, this field reference will return NULL (It is evaluated as Only(new_currentaum)).

If you want it to be evaluated once per dimensional value, you should use an aggregation function, e.g. Max():

=if(Max(new_currentaum)>=0, count(distinct [Organisation Name]), 0)

But if you want it to be evaluated once per record of raw data, you should put the If() inside the aggregation function, just as Dariusz Mielczarek suggests:

=count(distinct if(new_currentaum>=0, [Organisation Name]))

Set analysis is a third way to do it. It is like making a selection - but just for this expression. Then you should do what Mayil Vahanan Ramasamy suggests:

=count({<new_currentaum = {">=0"}>}distinct [Organisation Name])

But Set Analysis is not like a condition: So you cannot toggle between different expressions.

HIC

Re: Using if condition in an expression

Tue, 03 Jun 2014 13:27:09 GMT

Thanks Hic. I see your point and used the max function but I still get the same issue - it counts the organisation where currentaum = 0.

Re: Using if condition in an expression

MayilVahanan — Tue, 03 Jun 2014 13:29:40 GMT

Try like this

count({<new_currentaum = {">0"}>}distinct [Organisation Name])

Re: Using if condition in an expression

Tue, 03 Jun 2014 14:33:03 GMT

Thank you everyone for your help. Using just greater than zero works!

Regards

Jalps