topic Re: Expressions as variables. Error in QlikView

Expressions as variables. Error

tincholiver — Wed, 25 Nov 2020 16:16:04 GMT

Hello dear, I leave the following query

Why when I add an expression as a variable in the script, does it give me an error and if I do it from the variable panel is it ok?
The inconvenience I have with the expressions that contain set analysis, is it possible to do it?

Issue:

Any suggestion?

Thanks!

Re: Expressions as variables. Error

Gysbert_Wassenaar — Wed, 20 Feb 2019 16:11:01 GMT

It would help if you posted the part of the script that generated that. Most of use can't mind-read qlikview documents on other peoples computers.
What is going wrong are the single quotes. You need to either escape using double single quotes or chr(39) or another placeholder character that you later replace with the single quote

SET vMyVar = sum({<B={ @C@ }>} A);
LET vMyVar = Replace($(vMyVar), '@', chr(39));

But you're not done yet because $(...something...) will be evaluated in the script too and you don't want that. So you can use the replace trick again

LET vMyVar = 'sum({<B={ @C@ }, D={"<=%(=max(E))"}>} A)';
LET vMyVar = Replace(Replace($(vMyVar), '@', chr(39)),'%','$');

Re: Expressions as variables. Error

NW1965 — Wed, 20 Feb 2019 15:30:48 GMT

the problem is the single quote which tells the script that it is at the start or the end of a piece of text, so you need to swap that for a chr() reference such as:

VariableName = 'Sum({$<DistributionID={' & chr(39) & '143.3 etc etc etc

It can become quite long winded doing this in the script, but its the only way I've found to make it work.

Re: Expressions as variables. Error

tincholiver — Wed, 20 Feb 2019 15:55:34 GMT

here it's how i load the variable in the script:

LET vEMAES ='sum({<distribucion_id={'143.3'},Concepto={'Variac. Anual'},FECHA={'>=$(=AddMonths(Max({<distribucion_id={143.3}>} FECHA),-24))'} >}Valores)';

Re: Expressions as variables. Error

tincholiver — Wed, 20 Feb 2019 16:46:16 GMT

I found an interesting solution:
with this script you can load expressions from excel, since there are no problems with single quotes, it is also much easier to administer

Variable Table:
LOAD Variable,
Expression
DESDE
[C: \ Nube \ Wicham \ EQonomia \ Variables.xlsx]
(ooxml, embedded labels, table is Sheet1);

let CantVariables = NoOfRows ('Variable Table');
for i = 0 to (CantVariables-1)
let vVariable = Peek ('Variable', i, 'TableVariables');
let $ (vVariable) = Peek ('Expression', i, 'TableVariables');
NEXT

Thanks both!