Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Contributor

Re: Problem with Null() function

Hi Tutan,

Could you please post a sample for reference

Thanks

Manisha

Not applicable

Re: Problem with Null() function

isnull() returns 0 or -1, never returns so the condition is always missingSmiley Happy

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

chauhans85
Esteemed Contributor

Re: Problem with Null() function

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

Not applicable

Re: Problem with Null() function

Thanks a Lot Pari Pari...

Not applicable

Re: Problem with Null() function

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

MVP
MVP

Re: Problem with Null() function

Pari Pari schrieb:

isnull() returns 0 or -1, never returns so the condition is always missingSmiley Happy

=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

Re: Problem with Null() function

you're right

it was a huge missunderstanding on my side

what a shameSmiley Tongue

thanks Stefan

Not applicable

Re: Problem with Null() function

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

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

Community Browser