topic escaping script variable strings in QlikView

escaping script variable strings

Fri, 05 Nov 2010 14:45:24 GMT

I'm loading a script variable, say vS, with a load/peek which I then need to use in a subsequent load/SQL. The example below illustrates the problem using inline/resident rather than SQL data sources, but the issue is the same.

some_metadata:LOAD * INLINE [Sh'ello];temp:NOCONCATENATE LOAD SRESIDENT some_metadata;LET vS = PEEK('S', 0, 'temp');DROP TABLE temp;raw_data:LOAD * INLINE [raw_x,raw_y1,goodbye2,h'ello];data:LOAD raw_x as x, raw_y as yRESIDENT 'raw_data'WHERE raw_y = '$(vS)';

This works until S contains a single quote. I'm familiar with decomposing a string and then using '&' concatenation and CHR(39) in some contexts, but I'm not sure what to do in this case.

escaping script variable strings

Miguel_Angel_Baeyens — Fri, 05 Nov 2010 18:28:07 GMT

Hi,

try the following in your last WHERE clause

WHERE Replace(raw_y, chr(39), chr(39) & chr(39)) = '$(vS)';

Hope that helps.

escaping script variable strings

boorgura — Fri, 05 Nov 2010 18:37:17 GMT

Miguel,

that was a cool thing to know.

Thanks.

escaping script variable strings

Fri, 05 Nov 2010 19:05:57 GMT

Thanks, that works!

I assumed my problem was that the expansion of --'$(vS)'-- would be --'h'ello'-- and break the script. Since your suggestion worked, that suggests that QV is taking care of allowing script variables with single quotes to be expanded inside string literals (with single quotes) by doubling up the inner single quotes (which is also the SQL convention, I think). So the expansion becomes --'h''ello'--. Then doubling up the quotes that are literally present in the data evens things up for the comparison.

Can anyone comment on my interpretation of what QV is doing? Obviously thinking QV was doing the very simplest text substitution for the script variable expansion was wrong. It would be great to hear someone describe the complete picture. The documentation I've read doesn't go deep enough for me. For example, do single quotes always turn into pairs of single quotes during expansion, or only if the expansion is inside single quotes? Are there any other things to know about expansion?

escaping script variable strings

Anonymous — Fri, 05 Nov 2010 19:06:02 GMT

In similar cases, I simply remove single quote from the string completely:
LET vS = purgechar(peek(...), chr(39))
WHERE purgechar(ray_y, chr(39)) = ...

escaping script variable strings

Anonymous — Fri, 05 Nov 2010 19:25:47 GMT

You're right, QlikView script replaces
WHERE raw_y = '$(vS)'
with
WHERE raw_y = 'h'ello'
You can see this in the log file.

Now, the problem here is that the correct syntax
WHERE raw_y = 'h'
is followed by ello, which it doesn't understand at all. And, the last quota is interpreted as the opening of a string that maybe never ends - the script is broken.

escaping script variable strings

Fri, 05 Nov 2010 19:49:07 GMT

Thanks for your input. I'm confused. I tested what Miguel posted, and it works. How can what you're saying also be true? In my last post, I was saying that I had falsely assumed what you are describing would happen.

Do you have a test case which illustrates what you are stating in your post? I just tested it again. I trust you're seeing that in the log, but maybe the log isn't presenting things accurately. I added more side effects to observe what happens after the statement in question when the script variable contains a single quote. The never-closing single quote problem is not happening in my test.

escaping script variable strings

Anonymous — Fri, 05 Nov 2010 19:56:56 GMT

I don't see any contradiction between Miguel's solution and my statement. I'm certain that it works, only suggesting an alternative which I prefer. There are often many ways to achieve the same result.
As for the log - turrn the logging on, and see what will be created. I've never had a situation where log misrepresents actual script execution.

escaping script variable strings

Fri, 05 Nov 2010 20:26:45 GMT

Agreed; there are many ways to achieve the same result. I like the purgechar() approach too.

The contradiction involves your 11-05-2010 2:25 PM post. Miguel's solution works. It leaves the --'$(vS)'-- in the script. The value of S in the data table is --h'ello--, so by your statement, the script is broken when --'$(vS)'-- expands if vS contains a single quote. But it's not broken, and/or I'm making mistakes in my testing. Here's my new test case. Can you explain why it does work?

some_metadata:
LOAD * INLINE [
S
h'ello
];

temp:
NOCONCATENATE LOAD S
RESIDENT some_metadata;
LET vS = PEEK('S', 0, 'temp');
DROP TABLE temp;

raw_data:
LOAD * INLINE [
raw_x,raw_y
h'ello,goodbye
2,h'ello
];

data:
LOAD
 raw_x as x, raw_y as y
RESIDENT 'raw_data'
WHERE
 Replace(raw_y, chr(39), chr(39) & chr(39)) = '$(vS)' OR
 Replace(raw_x, chr(39), chr(39) & chr(39)) = '$(vS)';

vTest=999;

escaping script variable strings

Anonymous — Sat, 06 Nov 2010 01:27:28 GMT

OK, I just tried. Have to tell, that to my surpise, the line
WHERE raw_y = '$(vS)'
was translated to 'h''ello' - two quotes between h and ello, although the value of the variable is "h'ello", with only one quote. So, there is no never-closing quote, there are two - 'h', and 'ello'. Something to think about...
I also tried the purgechar() - worked as expected.

escaping script variable strings

Sat, 06 Nov 2010 03:28:47 GMT

Thanks for verifying what's happening.

The two ways I've seen for escaping the enclosing characters (single quote in this case) in a language token are: 1) have a different escape character (backslash is common; then to express a backslash you would double the backslash) or 2) double the enclosing character if it appears within the token. QV apparently uses the later method which is just like SQL string constants.

It would be nice to have this information documented in the reference manual.

Everything learned so far applies to the load script. But this isn't the end of my problems. Now try to take the --$(vS)-- and show it in a text box while it contains a single quote. None of the following works:

$(vS)
$(=vS)
=$(vS)
=$(=vS)
'$(vS)'
'$(=vS)'
='$(vS)'
='$(=vS)'

Any ideas?

escaping script variable strings

Anonymous — Sat, 06 Nov 2010 14:17:12 GMT

It will be
=vS

See attached

escaping script variable strings

Sat, 06 Nov 2010 19:49:32 GMT

Thanks, I misunderstood the reference manual and thought you always had to use dollar expansion with scripting variables.

escaping script variable strings

Anonymous — Sun, 07 Nov 2010 14:25:07 GMT

Sometimes $() is not necessary, usualy on the front end. And, if not for the apostrophe, your last two versions would be working as well.