Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
bdiamante
Contributor III
Contributor III

Script SET variable vs. Variable Manager Issue

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?

1 Solution

Accepted Solutions
kedar_dandekar
Creator
Creator

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

View solution in original post

3 Replies
Anonymous
Not applicable

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

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
Partner - Creator III
Partner - Creator III

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