Solved: $(#variable) when variable is NULL: general script... - Qlik Community

Report Inappropriate Content · ‎2010-12-13

I tested this with 10.00.8715.5 x64 and 9.00.7646.9 x64 on Windows 7 x64.

I need to load data from an Excel file; in this file a column might contain letters, numbers or be BLANK, I need to handle everything that is not a number as 0, I need to test for " <> 0" in the script.

In the script I'm using peek() to load the field value in a variable and then $(#variable) to force it to a number; however if the field is BLANK in Excel, and is then loaded as NULL in QlikView, then the use of the $(#variable) notation will cause a "general script error".

I'm attaching a small sample built for the sole purpose of showing the issue.

I found an ugly workaround you find in the attached file, but I don't know why it even works or is necessary.

The manual says
$(#variablename) always yields a legal decimal-point number reflection of
the numeric value of variablename, possibly with exponential notation (for
very large/small numbers). If variablename does not exist or does not contain
a numeric value, it will be expanded to 0 instead.

But then when the manual says "always" it's not really "always"?

Thanks

Miguel_Angel_Baeyens · ‎2010-12-15

Hello Giulio:

About LET and SET you have some examples in the reference manual, but as I mentioned above, SET stores the right part on the variable as it is and LET evaluates it before storing, then stores.

A very simple example is

SET vSetVar = 1 + 1; // when recovered, vSetVar contains the string "1 + 1"

LET vLetVar = 1 + 1; // opposedly, vLetVar now contains "2", because the right part is evaluable and evaluated

But $(vSetVar) or $(#vSetVar) will return as well "2", as the string is evaluable and the dollar expansion is what it does. In this case, the same happens with $(vLetVar) or $(#vLetVar) which will also return 2.

Now consider the following

SET vSetVar = 1 + A; // when recovered, vSetVar contains the string "1 + A"

LET vLetVar = 1 + A; // vLetVar now contains a null, because the right part is evaluated and returns a null

Now $(#vLetVar) will return null (its length is zero).

So probably the reference manual would be more accurate saying in addition something like

"...provided that the variable, if evaluated, returns a non null value..."

As far as I understand NullAsValue prevents the above to happen, returning an "existing" null value instead of the default non-existing (a null is not "empty", it simply is not, that's why the general script error above).

Certainly, the reference manual should add that NullAsValue works for variables as well as for fields instead of mentioning just fields. Feel free to contact support or your partner in order to feedback QlikTech about this.

Anyway, you can "force" a variable to be reevaluated using $(=varName). What it actually does is evaluating an expression an return its result. In the null cases above, $(#=vLetVar) will return zero (dollar sign numeric expansion for non-numeric value), following what the manual stands.

Hope this helps.

EDIT: Further to your original post: The manual says

If variablename does not exist or does not contain a numeric value, it will be expanded to 0 instead.

If "variablename" does not exist, but it's specified. When there is no variable at all (which happens expanding a null), there is a syntax error, and that's my guess for what's happening to you. In examples:

$(#)

Is what happens when the result of the variable evaluation is null. There is no variable name at all, existing or non-existing.

$(#vWrongVariable)

Assuming that vWrongVariable has not been declared anywhere (neither the script or the Variable Overview menu) should return zero, according to the manual. Well, it doesn't. So it's likely worth a mail to the support guys to check that part.

Anyway

$(#=)

Will return zero, as that's the numeric representation of an empty or null expression rather than an empty or null variable, as happened above.

View solution in original post

Miguel_Angel_Baeyens · ‎2010-12-13

Hello Giulio,

So it seems. If you remove the "#" you obviously get an error in the part

if $(vData) = 0 then

Because you are comparing "nothing" (null) to zero, and you always need a left part in this statement.

But if you use it, when it transforms it to number, QlikView cannot represent the null as a value, so the script breaks with that error.

As to the following

if '$(vData)' = '' then

Won't fail, because although vData is null or empty, quotes are still there, and '' (left part, variable empty) is equal to ''.

Anyway, if you SET the variable vData instead of LET it, that will work. LET always evaluates the right part and store the returned value in the variable, while SET stores the string, and using the dollar expasion you can evaluate its content. My guess is that evaluating a null is not zero, is a null (nothing), and so it should throw an error with

LET vData = null();

That won't happen if you SET it.

Hope that helps.

Report Inappropriate Content · ‎2010-12-15

Thanks for confirming that when the manual says "always" it isn't "always" so.

I don't understand where I should use SET instead of LET.

I did some more tests and discovered that using

nullasvalue *;

before loading the data will make $(#variable) notation work w/o any error.

Again, the manual is not so clear:

nullasvalue

"...The fact that null
values are normally not allowed to link to other null values can be suspended
by means of the nullasvalue statement. Thus the null values of the specified
fields are considered to be unknown rather than undefined when using the
nullasvalue statement...."

So it seems $(#notation) is ok for "unknown nulls" and not ok for "undefined nulls".

Miguel_Angel_Baeyens · ‎2010-12-15