Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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?
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);?
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.
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