Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Null() function

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

11 Replies
manisha_uit
Creator
Creator

Hi Tutan,

Could you please post a sample for reference

Thanks

Manisha

Not applicable
Author

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

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

SunilChauhan
Champion
Champion

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

Sunil Chauhan
Not applicable
Author

Thanks a Lot Pari Pari...

Not applicable
Author

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

swuehl
MVP
MVP

Pari Pari schrieb:

isnull() returns 0 or -1, never returns 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?

Not applicable
Author

you're right

it was a huge missunderstanding on my side

what a shame:P

thanks Stefan

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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