3 Replies Latest reply: Sep 4, 2013 10:10 AM by Cotiso Hanganu RSS

    Script SET variable vs. Variable Manager Issue

    Brian Diamante

      I have the following variable definition:


      SET eSGP = avg({1<school_id={$(vSchoolID)},subject_code = {$(=Only(subject_code))}>}sgp);


      vSchoolID is set above in my script.  When I run the script with this definition and then check the variable manager after the load finishes, the value becomes:


      avg({1<school_id={742},subject_code = {(internal error)}>}sgp)


      However, if I do not set the variable at all in the script and simply create the variable in the Variable Manager with the exact same value definition I list above, the variable is correctly set after the load completes.


      I suspect there is a timing issue here, but does anyone know why this happens?

        • Re: Script SET variable vs. Variable Manager Issue
          Michael Solomovich

          Hi Brian,


          Without going into the "why" questions - set analysis syntax is not applicable to the script.  It works only in the front end expressions.




          • Re: Script SET variable vs. Variable Manager Issue
            Kedar Dandekar

            Hi Brian,


            This is what I perceive, when you assign variables in your script, they get assigned as part of script-execution process. In this process, QlikView does the Dollar-sign expansion - $() before the script statement is evaluated, i.e. before it gets assigned to the variable. This replaces the contents of $() function with its value.


            Hence your expression changes from:

            SET eSGP = avg({1<school_id={$(vSchoolID)},subject_code = {$(=Only(subject_code))}>}sgp);


            avg({1<school_id={742},subject_code = {(internal error)}>}sgp)


            Whereas, variables defined in the variable manager do not get assigned as part of script-execution, hence they remain - as is.


            In case you still would like to assign the variable in the script, a workaround would be

            to use a Two-Step approach, as given below:


            //2 step approach: To prevent the $() evaluation:

            //Use ~ in place of $

            SET eSGP_Intermediate = avg({1<school_id={~(vSchoolID)},subject_code = {~(=Only(subject_code))}>}sgp);

            //Replace '~' back to '$', before setting the variable

            SET eSGP = "=Replace(eSGP_Intermediate, '~', '$')";




            • Re: Script SET variable vs. Variable Manager Issue
              Cotiso Hanganu

              Hi Brian,


              I assume you won't do this only for one expression, but for more...

              In such a case I would recommend to have a file (Excel,CSV,etc) where to define the formulas for all your variables, read in script the file and aftwards, iteratively, transform each row of you formula definition in a variable and, within the same loop, populate it's content with the expression you need.


              It will allow you to define very fast quite a lot of variables containing expressions.

              And also to create variations in a snap through a simple copy-paste-modify process inside an XLS file !

              Beyound all the above benefits, it should help you also with your issue...


              Sempre fi,