Problem with Null() function

[#1]

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

[manisha_uit]

Hi Tutan,

Could you please post a sample for reference

Thanks

Manisha

[#3]

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

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

[SunilChauhan]

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

[#5]

Thanks a Lot Pari Pari...

[#6]

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]

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?

[#8]

you're right

it was a huge missunderstanding on my side

what a shame:P

thanks Stefan

[#9]

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]

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