Dealing with null value in variable (inputbox)

[#1]

Hi guys

I just think that variable management isn't developed good enough in Qlikview or I just can't get it right (maybe the second affirmation is more probable) .

I get one variable defined in an input box and want to do some calculations with it and null case is a big deal for me, I attached a qvw file to illustrate the situation at a basic level but the expressions I'm using are:

- Left text box: =If($(=len(vVariable))=0, 'Empty', $(vVariable)) => Works good, shows variable value without a problem and 'Empty' when null

- Right text box: =If($(=len(vVariable))=0, 'Empty', 'Value: '&$(vVariable)) => Works good with value, when null I get Expression Error (as you first see)

This is pretty basic but if you replace the else case by another if condition using the vVariable get the same issue.

Do you know how can I correctly handle null value on this case?

Thanks

[awhitfield]

Hi Cristian,

there's no QVW attached to post.

[Gysbert_Wassenaar]

Set a constraint on the input box so the variable cannot be null. Use len($)>0 as Custom Input Constraint.

Or if your variable should contain a number perhaps you can use the alt function: alt($(vVariable), 1234). If vVariable does not contain a number the alt function will return the second parameter as value (1234 in the example).

[#4]

Hi Cristian,

use (isnull) function

[#5]

My bad, thanks for pointing that out. I edited first post to attach the file

[#6]

What I'm really doing is having filters by numbers of ocurrences of a set of hospitalary events and actually need null for exclude some events from my calculation so I can't validate that the input have a value and 0 is a valid value in the calculation.

=If(alt($(vVariable), -1)=-1, 'Empty', $(vVariable)) doesn't work for identify null values, also the problem isn't identifying them (len works just good for that), the problem is that in the else part, I can't use the variable when it has null value. Please see the file attached to my first post

[#7]

It was my first choice but there's a problem: apparently you can't invoke a function in a variable with null value.

I tried with this expression: =If(IsNull($(vVariable)), 'Empty', $(vVariable))

And got: Error: Error in expression:

IsNull takes 1 parameter

My guess is that the behaviour is like the variable doesn't exists at all in case of null value but can't find any function like PHP's isset. I also tried my len approach $(=isNull(vVariable)) without any luck

Please see the file attached to my first post to see the errors I'm having

[Gysbert_Wassenaar · Accepted solution]

Don't expand the variable in the expression in the text boxes:

=If($(=len(vVariable))=0, 'Empty', vVariable)

=If($(=len(vVariable))=0, 'Empty', 'Value: ' & vVariable)

[#9]

It works Gysbert thanks but why you don't have to expand the variable? I'm having hard times figuring out when to expand, when no.

My original expression was:

=If($(=len(vFiltroConsultaEspecialista))>0,

    If(COUNT({$<CodConsulta={5}, Año={$(=Max(Año))}>} DISTINCT IdActividad)>=vFiltroConsultaEspecialista, COUNT(DISTINCT IdAfiliado_Programa), 'No Value')

)

It works now without expanding in the inner If

[#10]

Try this...

I tried on your qvw and it worked.

find the attachment.

=if(vVariable='','Empty',vVariable)