Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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:
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;