topic Re: Stop Dollar Sign Expansion in the script (Escape Character ??? ) in QlikView

Stop Dollar Sign Expansion in the script (Escape Character ??? )

Thu, 04 Mar 2010 17:06:56 GMT

Hi there !

I have a problem in my script with the dollar sign expansion (eg. $(...))

To be quick, would like to know if there exists a way to stop the dollar sign expansion in my script. Is there an escape character ?

I explain :

let A = $(Today())

will evaluate the function so that A = 04/03/2010

I would like A to kepp the text : A = $(Today())

I tried the following, but It doesnt work :

let A = '$(Today())' --> just gives '04/03/2010'

let A = $$(Today()) --> just gives $04/03/2010

let A = \$(Today()) --> just gives \04/03/2010

Is there a solution ?

Stop Dollar Sign Expansion in the script (Escape Character ??? )

Miguel_Angel_Baeyens — Thu, 04 Mar 2010 17:25:06 GMT

Hello Stephane,

There is indeed. Use SET instead. SETting a variable means you store something in the variable. Then, you can expand it or not. So

SET A = $(Today())

and then, in a textbox for example setting just

=A

will show you "$(Today())" (without quotes).

On the other hand, LET evaluates the expression and then assign it to a variable, which is useful too, but not what you are looking for.

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

Fri, 04 Nov 2011 16:47:17 GMT

None of the above will work.

What you can do however is define the string you want in the script and use some other special character such as ~ to take the place of the $. Define a second variable which then references the first which replaces the ~ character with a $.

set vTodayString = "~(Today());

set vToday = "=replace([vTodayString],'~','$')";

It's a hideous way of doing it, but it should work. I did this when using a variable to store an expression containing set analysis, which used dollar expansion within the definition for the set. Letting the script do the dollar expansion was generating an "internal error", hence this approach.

I can't see why you would want to do this for such a simple function, but it should help others out who may be doing something a little more complex.

Here is my set analysis expression for example:

// Set analysis string using ~ in the place of $ to prevent expansion

set DailyDemandString = "sum({~<TransType = {~(SALESTRANS)}, PERIOD_DATE = {'>=~(=Date('~(Today)' - [NumberOfDaysForDemand]))<=~(Today)'}>} MO_Quantity * -1) / [NumberOfDaysForDemand]";

set DailyDemand = "=replace([DailyDemandString],'~','$')";

Stop Dollar Sign Expansion in the script (Escape Character ??? )

pablolabbe — Tue, 24 Jan 2012 12:35:15 GMT

Jacob,

Your tip worked perfect to me. I had a script that loads several formulas stored on a Excel Worksheet and I was dealing with this trouble for a while.

Thank you.

Pablo Labbe

Qlikview Consultant

Vision Gestão & Tecnologia

www.visiongi.com.br

Stop Dollar Sign Expansion in the script (Escape Character ??? )

Wed, 25 Jan 2012 12:17:06 GMT

Not a problem. I'm glad it worked for you

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

Anonymous — Wed, 21 Mar 2012 14:39:38 GMT

Wow, thankfully I wasn't the Only() one having this problem....

I have followed your recipe, but in order to get my new variable to work I must refer to it as $(variable). Is there any way to have variable be enough? (This is sufficient if I type the definition manually in the variable editor (CTRL-ALT-V)). The variable in question is, namely, being used in various nested expressions, so it would be nice to avoid this final dollar sign...

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

Wed, 21 Mar 2012 14:50:48 GMT

I believe you will have no option but to dollar expand the variable, as the idea is that you evaluate the expression within the variable and then use the result of that expansion to form your expression.

Using the square brackets just gets the literal value of the variable, without evaluating it, so the likelihood is it just wouldn't work syntactically.

I mean, I'm no expert, so anyone else from the forums who knows more about the technical syntax; feel free to pitch in and correct me...

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

Anonymous — Wed, 21 Mar 2012 15:07:59 GMT

Wow once again, and thanks for the quick reply! (Yes, I am aware of the email alerts... )

Actually, I didn't know that the []'s made a difference, so I might play around a bit with those --- you never know.

(For now these expressions are hardcoded since, as mentioned, I haven't always had luck with the $(variable) notation because of expression nesting. But hey, I certainly am no expert regarding the subtleties of the dollar sign, so if there are any experts around, any help would of course be much appreciated!)

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

Anonymous — Wed, 20 Jun 2012 08:06:16 GMT

HI,

Was having a similar problem nd have just found a workable solution.

My issue is that I wanted to create a way of translating text, chart titles, column titles etc on the front end between English and Swedish based on a variable choosen by the user.

After loading in a TransDict table from a spreadsheet, the script I used was as follows:

DO WHILE a < vTransRows

LET varName = Peek('Variable', a,'TransDict');

LET Eng_$(varName) = Peek('English', a,'TransDict');

LET Swe_$(varName) = Peek('Swedish', a,'TransDict');

LET vTransStr = '(vLang)_$(varName)';

LET Trans_$(varName) = '=$'&vTransStr;

LET a = a + 1;

LOOP

Prefixing the vTransStr with a dollar after I had initially created it means that the variables now read as follows:

Trans_ChartTitle1 = '=$(vLang)_ChartTitle1'

Trans_ChartTitle2 = '=$(vLang)_ChartTitle2'

Trans_ChartTitle3 = '=$(vLang)_ChartTitle3'

This means I can the change the value of vLang between 'Eng' and 'Swe' and show different text accordingly.

Hope this helps someone.

Regards,

Jonathan

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

Anonymous — Tue, 18 Sep 2012 07:59:59 GMT

Oh my god, it was that simple, eh -- why didn't I think of that?!

LET variable = '= only({$<Sales={"' & '$' & '(#=max(Sales))"}>} Town)';

Hooray!

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

Lauri — Wed, 15 Nov 2017 17:00:56 GMT

Still a valid solution 5 years later!

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

Ben_P — Thu, 21 Dec 2017 13:01:20 GMT

yep, I'm still using this, too!

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

kjhertz — Thu, 12 Jul 2018 13:29:53 GMT

Actually you can simplify this somewhat and remove the first & surrounding the $-sign:

LET variable = 'only({$<Sales={"$'&'(#=max(Sales))"}>} Town)';

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )

mjperreault — Tue, 24 Sep 2019 22:44:25 GMT

Does this still work with newer versions of Qlik Sense. When I try this using June 2019

SET A = $(Today())

A = <NULL>