Script SET variable vs. Variable Manager Issue

[bdiamante]

I have the following variable definition:

SET eSGP = avg({1<school_id={$(vSchoolID)},subject_code = {$(=Only(subject_code))}>}sgp);

vSchoolID is set above in my script.  When I run the script with this definition and then check the variable manager after the load finishes, the value becomes:

avg({1<school_id={742},subject_code = {(internal error)}>}sgp)

However, if I do not set the variable at all in the script and simply create the variable in the Variable Manager with the exact same value definition I list above, the variable is correctly set after the load completes.

I suspect there is a timing issue here, but does anyone know why this happens?

[Anonymous]

Hi Brian,

Without going into the "why" questions - set analysis syntax is not applicable to the script.  It works only in the front end expressions.

Regards,

Michael

[kedar_dandekar · Accepted solution]

Hi Brian,

This is what I perceive, when you assign variables in your script, they get assigned as part of script-execution process. In this process, QlikView does the Dollar-sign expansion - $() before the script statement is evaluated, i.e. before it gets assigned to the variable. This replaces the contents of $() function with its value.

Hence your expression changes from:

SET eSGP = avg({1<school_id={$(vSchoolID)},subject_code = {$(=Only(subject_code))}>}sgp);

to:

avg({1<school_id={742},subject_code = {(internal error)}>}sgp)

Whereas, variables defined in the variable manager do not get assigned as part of script-execution, hence they remain - as is.

In case you still would like to assign the variable in the script, a workaround would be

to use a Two-Step approach, as given below:

//2 step approach: To prevent the $() evaluation:

//Use ~ in place of $

SET eSGP_Intermediate = avg({1<school_id={~(vSchoolID)},subject_code = {~(=Only(subject_code))}>}sgp);

//Replace '~' back to '$', before setting the variable

SET eSGP = "=Replace(eSGP_Intermediate, '~', '$')";

HTH,

KD

[cotiso_hanganu]

Hi Brian,

I assume you won't do this only for one expression, but for more...

In such a case I would recommend to have a file (Excel,CSV,etc) where to define the formulas for all your variables, read in script the file and aftwards, iteratively, transform each row of you formula definition in a variable and, within the same loop, populate it's content with the expression you need.

It will allow you to define very fast quite a lot of variables containing expressions.

And also to create variations in a snap through a simple copy-paste-modify process inside an XLS file !

Beyound all the above benefits, it should help you also with your issue...

Sempre fi,

Cotiso