topic Problem with Null Values in QlikView

Problem with Null Values

Mon, 14 Jan 2013 04:44:13 GMT

Hi Guys,

I have a scenario, my expression is: count(A)+count(B).

If count(A) is null, the expression returns null even if count(B) has a value.

By right, I want the expression to return the value of count(B) whenever count(A) is null.

Can anybody suggest a solution to this problem?

Thanks

Re: Problem with Null Values

er_mohit — Mon, 14 Jan 2013 06:23:29 GMT

try this

count(A+B)

hope it helps

see the attached file..

Re: Problem with Null Values

nirav_bhimani — Mon, 14 Jan 2013 06:38:30 GMT

HI,

Try this

If ([count(A)]= Null(),[count(B)],IF([count(A)]= Null(),[count(B)], [count(A)] +[count(B)]))

Regards,

Nirav Bhimani

Re: Problem with Null Values

sujeetsingh — Mon, 14 Jan 2013 06:44:35 GMT

hi,

no such issue with mee..

well try this expression as

if(isnull(count(A)<>0,(Count(a) + count(B)),Count(B))

Hope it goes well..

111111

Re: Problem with Null Values

jonathandienst — Mon, 14 Jan 2013 07:16:58 GMT

To avoid calculating the counts more than once, you have two options

1. Use the Alt() function

=Alt(Count(A), 0) + Alt(Count(B), 0)

2.Use the Rangesum function

=RangeSum(Count(A), Count(B))

Regards

Jonathan

Re: Problem with Null Values

Sokkorn — Mon, 14 Jan 2013 07:20:03 GMT

Hi,

QlikView have a lot of functions for developer. NumSum( expr1 [ , expr2, ... exprN ]) ==> Returns the numeric sum of 1 to N arguments. As opposed to the + operator, numsum will treat all non-numeric values as 0.

Ex:

NumSum(1,2,4) returns 7

NumSum(1,'xyz') returns 1

NumSum(Null()) returns 0

So within your case, you can try: NumSum(Count(A),Count(B))

Hope this help and let me know.

Regards,

Sokkorn

Re: Problem with Null Values

jonathandienst — Mon, 14 Jan 2013 07:23:12 GMT

Rereading your post, you say you want to return the value of Count(B) if Count(A) is null (ie not Count(A) + Count(B)?

Then, again to avoid calculating Count(A) twice, you can use

=Alt(Count(A), Count(B), 0)

This will return Count(A) if not null, otherwise Count(B), or zero if both are null.

Regards

Jonathan

Re: Problem with Null Values

Mon, 14 Jan 2013 08:22:06 GMT

Huge thanks bro.

Re: Problem with Null Values

Mon, 14 Jan 2013 08:29:06 GMT

Thank you Sokkorn,

Your suggestion is greatly appreciated, but actually the formula I have in hand is much more complicated than count(A)+count(B), that is a simplified scenario that I thought of.

So under my real case, NumSum() is not really applicable since the expression involves weighted average.

Thank you and I will try NumSum() in some future cases definitely.

Regards,

Jason