Variables which reference other variables in the script

sam_grounds — Thu, 14 Sep 2017 14:47:08 GMT

Hi all,

I've had a pesky issue in the past with the dollar expansion. A great feature, but can cause problems if you don't know how to avoid it... I often want to write a large number of variables, which I paste into my script, sometimes using a program like excel to use a formulaic pattern to writing them.

An example would be this...

Variable name: vSales

Desired value: num(sum({$<[Transaction Date]='$(vDate)'}>}Sales),'£#,##0')

There a couple of problems with this approach, the first of all is the use of single quotes. If you want to write a single quote into a variable using the script, use the chr(39) function instead.

Simply replace all single quotes with this

'&chr(39)&'

(including the single quotes)

Secondly, the dollar expansion will evaluate the variable there and then and write it in, in place of the variable name, rather than allowing it to reference the variable directly.

In order to stop the dollar expansion evaluating the string contained in the variable, simply break the dollar and opening bracket up. Like so...

$'&'(

Simple, yet effective.

So the best way I have found to write this into the script would be...

LET vSales_Yesterday='num(sum({$<[Transaction Date]={'&chr(39)&'$'&'(vDate)'&chr(39)&'}>}Sales),'&chr(39)&'£#,##0'&chr(39)&')';

This will create a variable...

vSales

which contains...

num(sum({$<[Transaction Date]={'$(vDate)'}>}Sales),'£#,##0')

This approach basically tweaks what is being evaluated the first time (by the script) to produce a string which later on when it is evaluated it can repeatedly use the other variable in the front end expressions.

Hope this helps someone else who encounters the same issue.

Sam

Re: Variables which reference other variables in the script

millerhm — Fri, 22 Oct 2021 01:48:16 GMT

@sam_grounds - four years later, you've saved me from tearing all of my hair out or banging my head on my desk one too many times - many, many thanks!

Re: Variables which reference other variables in the script

jda_bryan — Tue, 18 Oct 2022 20:07:50 GMT

@sam_grounds Same here. Nice tip. Thanks!

topic Re: Variables which reference other variables in the script in QlikView

Variables which reference other variables in the script

Re: Variables which reference other variables in the script

Re: Variables which reference other variables in the script