2 Replies Latest reply: Jan 26, 2016 6:55 PM by Paul Scotchford RSS

    SET is evaluating a variable within the string

    Christine Dohmen

      I have an Excel file that contains all of the app's calculations.  During the data load process it loops through those calculations and creates the variables.  In Excel vName = 'vMyNewVariable' and vExpression = 'SUM({<[Year] = >} Spend)'.  Then I run the SET statement.  SET $(vName) = $(vExpression);.  So now I have a new variable I can use in the app called $(vMyNewVariable).

       

      That works great and all but now I want to put a variable name in the expression and I want the variable name to stay the variable name and not evaluated into the value.

       

      vName = 'vMyNewVariable'

      vExpression = 'SUM({<[Year] = {'$(vYearSelected)'}>} Spend)'

       

      Then...


      SET $(vName) = $(vExpression);

       

      Which turns into...

       

      SUM({<[Year] = {''}>} Spend)


      I want...

       

      SUM({<[Year] = {'$(vYearSelected)'}>} Spend)


      I was under the impression that SET did not do any evaluation on the string.  Are there any workarounds? 

       

      FYI, the reason I want the variable name to remain is because within the app I have a variable button that changes the variable value on the fly based on the user's needs.

       

      Thanks is advance!!

        • Re: SET is evaluating a variable within the string
          Stefan Wühl

          A dollar sign expansion is a text replacement that happens before the actual statement is parsed and evaluated.

          Hence it will take place before QV evaluated the SET / LET statement.

           

          What you want to do should work if you are using an excel file as source for your expressions and then create your variables using PEEK() to access the table rows. Like shown e.g. here:

          Can I SET or LET a variable to include a $(=...)

          • Re: SET is evaluating a variable within the string
            Paul Scotchford

            Hi Christine,

             

            Below is the code I use to do exactly what you are attempting with your excel.

            This is from an Business Definitions dictionary implementation I built for a client, if you would like a full

            spec (complentary) , please contact me at paul.scotchford@awari.com.au

             

              BusinessDictionary:

              LOAD

                  app_name, // The app these variables apply to

                  app_brief, // Brief description of the app

                  def_abbrev, // Business definition abbrev

                  def_name, // Business definition name

                  def_brief, // Business definition description

                  var_name, // Variable name

                  var_label, // Variable label (hopefully QlikSense matures to allow dynamic labelling)

                  var_expression, // The expression of the variable i.e the Calc

                  var_type, // If the variable is a constant then it is a string e.g. max(claim_notify_year)

                  var_brief, // Desription of the variable   (doco)

                  var_english_calc // Psuedo englsh calc (doco)

              FROM [lib://Testing/BusinessDictionary.xlsx] (ooxml, embedded labels)

              where app_name  = '$(vAppName)'

             

              Let RowCount = NoOfRows('BusinessDictionary');

             

             

              if RowCount > 0 then

              For i=0 to '$(RowCount)'

                    

                let vType  = peek('var_type',$(i),'BusinessDictionary');

                let vName  = peek('var_name',$(i),'BusinessDictionary');

                let vValue = peek('var_expression',$(i),'BusinessDictionary');

                     

                if vType = 'string' then

                   let $(vName) = '=' & lookup('var_expression','var_name', '$(vName)', 'BusinessDictionary');

                  else

                   let $(vName) = lookup('var_expression','var_name', '$(vName)', 'BusinessDictionary');

                end if

              next i

              end if;