topic Re: Problem with Null() function in QlikView

Problem with Null() function

Sun, 29 Jul 2012 21:12:24 GMT

Hi All,

I have a strange problem which I am really not being able to figure out and I request help.

I have an expression which displays variables in this way ,

if($(vYTDTarget_CY_Line_2.b_TS) = 0,null(),

if($(vYTDTarget_CY_Line_2.b_TS) > 0, num(($(vYTD_CY_Line_2.b_TS)-$(vYTDTarget_CY_Line_2.b_TS))/$(vYTDTarget_CY_Line_2.b_TS),'#,##0.0%')

if($(vYTDTarget_CY_Line_2.b_TS) < 0 , num(($(vYTDTarget_CY_Line_2.b_TS)-$(vYTD_CY_Line_2.b_TS))/$(vYTDTarget_CY_Line_2.b_TS), '#,##0.0%'))))

According to the values, I have vYTDTarget_CY_Line_2.b_TS evaluate to 0.

I have stored this expression in a variable vLine2b_grade_TS which when I display in a text object as $(vLine2b_grade_TS) I get a - sign ( As I have declared to have all null as " - ")

Now, when I use the following expression in a text box,

if(IsNull(vLine2b_grade_TS), null(),

if(text(vLine2b_grade_TS)='-0.0%',2,

If(vLine2b_grade_TS >= 0,4,

if(vLine2b_grade_TS < -.05,0, 2))))

I get a value of 4. Qlikview is taking the value of vLine2b_grade_TS as 0 instead of null(). Ideally this value should be 0 instead of 4.

Request all your help and suggestions to resolve this.

Thanks,

Tutan

Re: Problem with Null() function

manisha_uit — Mon, 30 Jul 2012 07:40:02 GMT

Hi Tutan,

Could you please post a sample for reference

Thanks

Manisha

Re: Problem with Null() function

Mon, 30 Jul 2012 12:57:58 GMT

isnull() returns 0 or -1, never returns 1 so the condition is always missing:)

=if(IsNull(vLine2b_grade_TS)=-1, null(), ......

Re: Problem with Null() function

SunilChauhan — Mon, 30 Jul 2012 13:07:17 GMT

use below

if(IsNull(vLine2b_grade_TS), ' ',

if(text(vLine2b_grade_TS)='-0.0%',2,

If(vLine2b_grade_TS >= 0,4,

if(vLine2b_grade_TS < -.05,0, 2))))

hope this helps

Re: Problem with Null() function

Tue, 31 Jul 2012 22:01:35 GMT

Thanks a Lot Pari Pari...

Re: Problem with Null() function

Wed, 01 Aug 2012 04:58:10 GMT

Tutan,

On some other circumstance the IsNull() function doesn't work at all. It is safe to use len() function instead.

e.g. if(len(MyField) < 1, 'Field is Null)

Regards,

Bernardine

Re: Problem with Null() function

swuehl — Wed, 01 Aug 2012 09:46:23 GMT

Pari Pari schrieb:
isnull() returns 0 or -1, never returns 1 so the condition is always missing:)
=if(IsNull(vLine2b_grade_TS)=-1, null(), ......

I believe

IsNull( NULL() )

and

IsNull( NULL() ) = -1

both return the same: -1 which is the numerical representation of TRUE in QV world (check true() function).

(in fact, I think all numericals not equal zero are interpreted as TRUE, only zero is interpreted as FALSE)

So I can't really see how this solved the problem.

tutan4all, it seems nevertheless that your issue has been solved by changing it to above, which leaves me clueless, or have you changed anything else in addition?

Re: Problem with Null() function

Wed, 01 Aug 2012 10:44:18 GMT

you're right

it was a huge missunderstanding on my side

what a shame:P

thanks Stefan

Re: Problem with Null() function

Wed, 01 Aug 2012 14:42:57 GMT

Hi Stefan,

I just changed the condition yesterday and had a feeling it worked for one instance, as I checked for another instance today, I saw this to fail. You are absolutely correct. I am looking for an alternate solution.

Tutan

Re: Problem with Null() function

rwunderlich — Wed, 01 Aug 2012 15:24:08 GMT

IsNull(vLine2b_grade_TS)

If I understand correctly, vLine2b_grade_TS is a variable. Variables can never be null, so this test is not useful.

Bernadine's suggestion of using a len() test will work with variables as well as fields. In which case I would code it as:

if(len('$(vLine2b_grade_TS)'),...

-Rob

http://robwunderlich.com

Re: Problem with Null() function

swuehl — Wed, 01 Aug 2012 15:48:27 GMT

Variables can never be null, so this test is not useful.

Rob, are you sure about that?

If I define a variable vTest in variable overview like

=NULL()

I can test the variable in an object

=isNull(vTest)

And get -1 / True back.

But I would also give your suggestion a try, though I would also double check the definition and value of vYTDTarget_CY_Line_2.b_TS since this will determine everything else.

tutan4uall, could you create and post a small sample app?

Re: Problem with Null() function

rwunderlich — Wed, 01 Aug 2012 17:25:42 GMT

...Rob, are you sure about that?..

Obviously not

You can assign something that resolves to null in the variable overview as you've indicated. You can also use "=xxx", which resolves to null. Other setting methods - LET, Actions, can do that if they assign the string "=null()" to the variable.

But nothing I know of, including peek(), can assign a direct null value to a variable. So it may be impractical and probably error prone to think of variables as being able to hold null().

-Rob