SET is evaluating a variable within the string

[Anonymous]

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!!

[swuehl]

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 $(=...)

[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;