topic Re: Variables in the Script in QlikView

Variables in the Script

sunny_talwar — Sun, 21 Feb 2016 20:35:59 GMT

Hi Team -

I am going to confess that I am not an expert on LET and SET statements and find it amazingly difficult to understand what will work and what won't. I know the basic difference between the two that LET evaluates, whereas SET will just assign a value to a variable. But when I have to do some letting or setting, I always end up with a trial and error method of getting it right rather then knowing what is right.

My question today isn't related to LET and SET specifically, but I have a feeling that we are going to drive this discussion toward the use of LET vs. SET. Here is the script I am working with:

//Works

LET vVar2 = Chr(39) & 'ABC' & Chr(39);
LET vVar3 = Chr(39) & If($(vVar2) = 'ABC', 'DEF', '') & Chr(39);

// Doesn't Work

LET vVar1 = 'ABC';
LET vVar4 = Chr(39) & If(Chr(39) & $(vVar1) & Chr(39) = 'ABC', 'DEF', '') & Chr(39);

For sum reason, vVar3 seems to work as I would expect, but vVar4 won't. Is there a reason why one would work and the other one won't?

Thanks for your expert advice on the topic.

Best,

Sunny

Message was edited by: Sunny T Added the sample application

Re: Variables in the Script

swuehl — Sun, 21 Feb 2016 20:50:57 GMT

Having issues with SET / LET / variable expansions etc. it's helpful to run the script in debug mode.

The line with variable vVar4 will expand to

LET vVar4 = Chr(39) & If(Chr(39) & ABC & Chr(39) = 'ABC', 'DEF', '') & Chr(39)

Can you already pinpoint the cause of the issue? Examine the replaced variable: Is it a valid literal string or something else?

Re: Variables in the Script

sunny_talwar — Sun, 21 Feb 2016 21:12:33 GMT

So I guess what can I replace vVar1 in order for vVar4 to work? I tried SET vVar1 = 'ABC';, but that did not work as well. Is the only way is to do this by LET vVar2 = Chr(39) & 'ABC' & Chr(39);?

Re: Variables in the Script

petter — Sun, 21 Feb 2016 21:19:53 GMT

I agree this is often confusing and can create a lot of headaches....

If you look at the critical part in line 9:

Chr(39) & $(vVar1) & Chr(39)

$-sign expansion is always done before QlikView try to interpret anything else.

So this will become:

Chr(39) & ABC & Chr(39)

The QlikView tries to interpret and would then think that ABC is a variable.

It can't find a variable named ABC so it evaluates to an empty string...

And then QlikView will finally end up with '' as the result.

If you change line 09 to this:

LET vVar4 = Chr(39) & If( vVar1 = 'ABC', 'DEF', '') & Chr(39);

This will work as you intend. Why would we want to make it more complicated by not accessing the variable value directly? Using $-sign expansion should only be used when it is absolutely necessary as it often complicates things.

So my advice is to use direct variable references without $-sign expansion whenever you can and only use $-sign expansion when you have to. It is necessary to use $-sign expansion sometimes because QlikView can't understand that we want the contents of a variable to be used inside for instance a string or a Set Expression. But you always have to remember that $-sign expansion is performed first before QlikView even tries to understand what the text in the load script or an expression in the UI really means.

$-sign expansion works as if we really did the substitution ourselves and only after that the normal QlikView interpretation will begin.

Re: Variables in the Script

sunny_talwar — Sun, 21 Feb 2016 21:35:38 GMT

Using $-sign expansion should only be used when it is absolutely necessary as it often complicates things.

To your point, I was not even aware that we can use a variable without a dollar sign expansion in the script. I have learnt something new today because before today I thought that variable in the script would look grey italicized. But that seems not to be true:

In the above example, I see that vVar1 (on line 27) looks like a field, but it actually is a variable.

Thanks for sharing the logic and explaining it in detail.

Best,

Sunny