Summary: In Qlik script SET is often a better choice than LET, even when the value contains quotes.
I sometimes see the LET script statement used when SET would be syntactically easier and more readable.
A brief review: SET assigns the given parameter as-is to the variable, LET treats the parameter as an expression and assigns the evaluated result to the variable.
SET x = 1+3; // x is "1+3" LET x = 1+3; // x is "4"
I frequently see a variable assignment like this:
LET eSales='sum(Sales)';
eSales stores an expression that will be used later in charts. It could also be written (simpler in my estimation) as:
SET eSales=sum(Sales);
So far just a matter of style, but the difference becomes clear when we have quotes as part of the string, for example, “Region={‘US’}”. As LET requires a quoted string, embedded quotes require some sort of escaping. In QV10 and earlier, a common way to write this with LET would be:
LET x = 'Region={' & chr(39) & 'US' & '}';
Not real pretty. Many people carry over this style even though QV11 introduced two single quotes to represent an embedded single quote.
LET x = 'Region={''US''}';
Easier to read for sure. But I think it’s even easier with SET.
SET x = Region={'US'};
That’s it. No special escaping required, just type it as it should be. What about those quotes? Shouldn’t SET strings be enclosed in quotes?
I find the documentation on SET to be thin, but here is the rule as I understand it.
Single or Double quotes in a SET statement require no special treatment as long as they are balanced (even number of quotes).
SET x = Region={'US'},Product={'Shoe'}; // Valid
SET x = Region={"U*"},Product={'Shoe'}; // Valid
SET x = I won't go; // Invalid
If the quotes are unbalanced (odd number), then the entire string needs to be enclosed in quotes or brackets. Use double quotes if we are enclosing single quotes.
SET x = "I won't go"; SET x = [I won't go];
I always favor SET over LET unless I truly want an evaluation. An exception to this is the string “$(” which will trigger an Dollar Sign Expansion, even in SET.
-Rob
For more on character escaping in Qlik from HIC see https://community.qlik.com/blogs/qlikviewdesignblog/2015/06/08/escape-sequences
Great post and clear.
Yes. I’ve often come across the same thing with stored rgb() values in variable, some using SET and LET, even seen both types in one doc. The behavior can be very different when used later on in the objects.
Cheers.
I see inconsistent patterns in rgb() variables as well. I sometimes see:
LET vG=’rgb(0,128,0)’;
which for me should be coded as:
SET vG=rgb(0,128,0);
However I usually use this form (without the quotes),
LET vG=rgb(0,128,0)
which allows the variable to be used in color expressions as simple reference, without the $().
if(column(1)>.8, vG)
Another simple yet often misused construct, myself being one of those who always overlooked these kind of things.
Time to revisit the script again!
Keep up the fantastic posts!
Rob,
Talking about Dollar Sign Expansion, is there any tricky way to avoid it while using SET/LET?
I usually do this
Set v = sum({}Value);
Let v = replace(v,’&’,’$’);
but it seems to be too odd to me
You can do the replace and variable setting in a single statement:
Let v = replace(sum({@}Value),’@’,’$’);
Or use a mapping table with MapSubstring:
EscapeCharsMap: MAPPING LOAD * INLINE [
from, to
@,$
|,’
];
LET vLastMonthSales = MapSubString(‘EscapeCharsMap’,}Sales)’
‘sum({
);
Best of all is to load from an external file, then you don’t need to worry about $ and ‘