4 Replies Latest reply: Dec 21, 2010 2:09 PM by Giulio Orsero RSS

    $(#variable) when variable is NULL: general script error, why?

      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"?



        • $(#variable) when variable is NULL: general script error, why?
          Miguel Angel Baeyens de Arce

          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.

            • $(#variable) when variable is NULL: general script error, why?

              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:


              "...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".


                • Re: $(#variable) when variable is NULL: general script error, why?
                  Miguel Angel Baeyens de Arce

                  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.




                  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.






                  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.

                    • $(#variable) when variable is NULL: general script error, why?


                      I understand let/set differences; I was confused because I couldn't understand how to use set with peek(), but I see you were just making an example.

                      Thanks for confirming about the manual being a bit cryptic at times, at least I know I'm not misunderstanding everything.

                      As for contacting support...unfortunately my support contacts are a pain to talk to (they regularly don't answer emails and such) and I prefer not to have to do with them unless absolutely necessary :(