topic Re: how to escape a semicolon? in App Development

how to escape a semicolon?

mmarchese — Tue, 13 Feb 2018 16:19:37 GMT

I'm trying to build a string with a semicolon in it. Is it possible?

Here are my failed attempts, which all produce this error: "Unknown statement: World".

Let a = 'Hello ; World';
Trace $(a);
Let a = 'Hello ' & chr(59) & ' World';
Trace $(a);
Let a = Replace('hello ~ World', '~', chr(59));
Trace $(a);
Let a = 'Hello ;; World';
Trace $(a);
Let a = 'Hello \; World';
Trace $(a);

The last 2 were just desperate grasps at straws based on the fact that quotes can be escaped by doubling them up (even though the syntax highlighter has no clue).

I also tried using Set instead of Let and then doing the Let in a second step, with the same outcome:

Set a = 'hello ' & chr(59) & ' there';
Let b = $(a);
Trace $(b);

For reference, the same statements work fine if I use a colon instead. They print "Hello : World".

Let a = 'Hello : World';
Trace $(a);
Let a = 'Hello ' & chr(58) & ' World';
Trace $(a);
Let a = Replace('Hello ~ World', '~', chr(58));
Trace $(a);

Re: how to escape a semicolon?

sunny_talwar — Tue, 13 Feb 2018 16:27:52 GMT

This works on the front end, are you specifically looking to make this work in the script?

SET a = 'Hello @ World';

SET b = '=Replace(''$(a)'', ''@'', '';'')';

TRACE $(a);

TRACE $(b);

Re: how to escape a semicolon?

mmarchese — Tue, 13 Feb 2018 16:41:54 GMT

I copied and pasted that in and it does not work for me. Everything after the final closing parenthesis in the 2nd line is highlighted in red. Maybe this was expected? I don't know - not sure what you mean by the front end.

The end goal is to use it in a load statement to make 3 new columns based on 2 existing columns. The new columns are HTML for use with an extension that shows images in a table (and also accepts raw HTML). The problem is that the HTML has a semicolon in it and Qlik hates it.

I was trying to make a little function to do the string substitution and then call the function 3 times. A simplified example:

Set GeneratePhotoHTML = 'Pretend this ' & $1 & ' is ' & $2 & ' ;HTML';
SomeTable:
Load
    ProblemNo,
    $(GeneratePhotoHTML(ProblemNo, 'A')) AS PhotoA,
    $(GeneratePhotoHTML(ProblemNo, 'B')) AS PhotoB,
    $(GeneratePhotoHTML(ProblemNo, 'C')) AS PhotoC
SELECT
...

I got this idea from here:

User-defined functions equivalent in Qlik Sense

Re: how to escape a semicolon?

Gysbert_Wassenaar — Tue, 13 Feb 2018 16:45:41 GMT

Looks like a bug to me.

This seems to work:

temp:LOAD 'Hello ; World' as Value AutoGenerate 1;

LET a = peek('Value',-1,'temp');

DROP TABLE temp;

Re: how to escape a semicolon?

mmarchese — Tue, 13 Feb 2018 16:54:18 GMT

It doesn't work for me. I still get the error "Unknown statement: World".

Re: how to escape a semicolon?

Gysbert_Wassenaar — Tue, 13 Feb 2018 16:58:50 GMT

Which version of Qlik Sense?

September 2017 release works for me here.

Re: how to escape a semicolon?

sunny_talwar — Tue, 13 Feb 2018 16:58:54 GMT

It seems that setting the variable isn't erroring out, it is using the variable which is causing the trouble, is that right?

Running this along did not throw any error

LET a = 'Hello ; World';

Re: how to escape a semicolon?

mmarchese — Tue, 13 Feb 2018 17:04:40 GMT

I have June 2017.

Re: how to escape a semicolon?

mmarchese — Tue, 13 Feb 2018 17:27:41 GMT

Yes, you are correct. It seems that using the variable inside of $( ) triggers the error.

I finally decided to give up on using trace for debugging and instead just try loading my data and seeing what happened.

Let a = 'Hey ; there ';
Set GeneratePhotoHTML = 'Pretend this ' & $1 & ' is ' & $2 & ' ;HTML';
SomeTable:
Load
    ProblemNo,
    $(GeneratePhotoHTML(ProblemNo, 'A')) AS PhotoA,  // I was not expecting this to work, but it does
    'Hello ; World' AS PhotoB,  // also works
    $(a) AS PhotoC;  // does not work
SELECT
...

I'm going to put the real code in there and make sure it still works. I might be good to go despite this bug. I'm confused but not at all surprised. I already knew that Qlik's set/let/string/number/literal/variable/dollar-sign-expansion/trace system was a total mess.

Re: how to escape a semicolon?

NoEyeDeer — Tue, 17 Dec 2019 13:09:53 GMT

Bit late, but to be complete and as a reference for other people landing here ... did you all realize that mmarchese used the expanding mechanism after a "TRACE"? Trace does accept everything "up to the next semicolon" as argument. So - indeed - the problem was the way mmarchese used the $()-expansion of the variable. And this also applies to the LOAD statement examples. In both cases single quotes surrounding the $() - like "Trace '$(a)';" or "'$(GeneratePhotoHTML(ProblemNo, ''A''))' AS PhotoA," (double ' inside '...' to encode a single ' inside a string) - should fix the problem (did not check that but am quite sure). However Trace will include the single quotes into it's logging output - a little fly in the ointment so to speak (I found this searching for an Idea to fix that).