Can I create a variable that holds another variable?

[Richard_Norris]

I have a variable in the frontend, vMD which holds the =Max(Date).

It means I can do "Sum(If(Date = '$(vMD)', Value))" and that works great.

What I'd really like to do though, is in the LOAD script, programmatically create lots of variables to hold measures. I actually want to create a SUB to be able to create these measures, but that's down the line. 

First of all, I just need to be able to put something in the load script that says:

SET vNewVar = "Sum(If(Date = '$(vMD)', Value))"; 

Such that in the frontend it works. I guess, what I want is to just store the TEXT, in pure TEXT form, so it appears AS I TYPE IT without doing any dollar expansion, such that in the frontend I can then use this text as the text for the measure. 

But whatever I try, it evaluates the $(vMD) to nothing and fails. If I try concatenating bits of it together, it doesn't evaluate that concatenation, so I end up with

'$' & (vMD)' & ', Value)' 

(or whatever) as the evaluation, rather than the concat. 

It feels like this must be something people have tried to do before, but I can't find a good example. Any help massively appreciated!

[Adam_Romanowski · Accepted solution]

Some time ago I did few experiments, and I have written simple mechanism to create variables automatically.

It will not work with definitions written in Qlik script. You need excel file with 3 columns: name, definition and flag field fExpand.

The trick is to use replace() function, but first parameter is a variable name without expand (dollar sign), and two next parameters of replace() function has text which does not exists in definition.

Spoiler

variables:
LOAD
name,
"definition",
fExpand
FROM [lib://Data/vars.xlsx]
(ooxml, embedded labels, table is Sheet1);

for i = 0 to NoOfRows('variables')-1
let vSourceVarName = Peek('name', $(i), 'variables'); // name
let vSourceVarDef = Peek('definition', $(i), 'variables'); // definition
let vExpand = Peek('fExpand', $(i), 'variables'); // expand?
if vExpand = 0 then
let $(vSourceVarName) = '$(vSourceVarDef)';
else
let $(vSourceVarName) = replace(vSourceVarDef, 'blahblah', 'Blahblah'); // trick to not expand variable
endif
next i;

I hope it will be helpful.

[rwunderlich]

You can avoid using the Replace by using peek to get the Def:

FOR i = 0 to NoOfRows('Variables')-1
  LET vVarname = peek('Varname',$(i),'Variables');
  LET $(vVarname)=peek('Varvalue',$(i),'Variables');
NEXT i

-Rob

[Richard_Norris]

Amazing, thanks so much guys! I used replace just because with how messy my formulas are, it's a bit easier to tell what's going on having it laid out in clearly labelled steps, but I'll bear that in mind for the future Rob!