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.

           

          Regards,

          Michael

          • 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);

            to:

            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, '~', '$')";

             

            HTH,

            KD

            • 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,

              Cotiso