Can I SET or LET a variable to include a $(=...)

[#1]

I want my variable, vPop, to look like this:

SUM({$<Year={$(=MAX(Year))}>}Population)

Notice the part in bold.

See in attached ; open the variable view

Can I do this in SET or LET in the Load Script

When I try this:

SET vPop = SUM({$<Year={$(=MAX(Year))}>}Population);

After reloading, the value of vPop is this:

SUM({$<Year={}>}Population)

And therefore $(vPop) evaluates to 0

I managed to do it by separating "$"  from the "("  and concatenating them together -- but it's ugly.

[tresesco]

Yes '$' would cause expansion, so you have to use a bit ugly way, like:

LET vPop = 'SUM({<Year={'&chr(36)&'(=MAX(Year))}>}Population)';

[tamilarasu]

You can use any symbols instead of dollar sign and replace it by using replace function like below,

LET vPop = Replace('SUM({<Year={@(=MAX(Year))}>}Population)', '@' , '$');

[Peter_Cammaert · Accepted solution]

This should work too, since the $-sign substitution is applied to the raw statement text, before the statement is actually parsed and evaluated:

LET vPop = 'SUM({<Year={' & '$' & '(=MAX(Year))}>}Population)';

BTW these are all tricks to sort-of mislead the $-sign expansion process. For the moment, that is.

[#5]

All of these answers solve the issue. I'd like to mark them all correct. I used @Peter's approach, so I'll pick that one.

I do like @Tamil's approach because it does not require string concatenation -- it does not break up the "flow" of the statement. None are as elegant as I was hoping for.

Thanks all!

[#6]

And Steve Dark mentions the same, here:

http://www.quickintelligence.co.uk/qlikview-include-files/

The simplest way to not have it do the replace is use a different character for the $ symbol, and then replace it afterwards, ie:

set vMyVariable = sum(¬(vSelectedMeasure));
let vMyVariable = replace(vMyVariable, ‘¬’, ‘$’);

[#7]

Note: This requires the use of `LET` (not SET)

[Peter_Cammaert]

You do have a point of course. QlikView script is not an elegant language, far from it. It's a collection of good ideas welded together. You already know them: QlikView native script, SQL, VBScript and a sort of preprocessor that handles text substitution and which is a typical compiler technique patched onto an interpreter. The surprising thing is that it works and it works pretty well.

On the other hand, the knowledge required to predict how a specific script will interact/behave is pretty vaste. That's why I think the community is an invaluable resource. So go on, and ask questions. You're welcome.

Enjoy QlikView and the Qlik Community.

Peter

[#9]

Yeah, I guess I'm not an expert on "elegant".

In fact I quite like the text-substitution, I think the adjective that comes to mind is "powerful". Esp. in combo with all the other stuff you mentioned.

I guess I was just hoping for an escape character, $\(...)

or a switch to turn off the $ substitution behavior for a few lines (and turn it back-on again) --ala QUALIFY and UNQUALIFY -- DOLLAR_SUBSTITUTION_OFF and DOLLAR_SUBSTITUTION_ON

I guess I should go to Qlikview feature suggestion -- I'm curious if others have faced this too, and if they have other suggestions besides escape character or a "switch"

But string concatenation and string substitution both allow me to do what I need, "powerful", just a little stilted reading the script

[swuehl]

You can put all your variables (or at least the ones with chart expressions, potentially containing dollar sign expansions) into e.g. an excel file with two columns and read the excel in like

Variables:

LOAD Variable,

    Expression

FROM

[Variables.xls]

(biff, embedded labels, table is Tabelle1$);

Let vNo = NoOfRows('Variables');

For vI = 0 to (vNo - 1)

Let vVariableName = Peek('Variable',vI,'Variables');

Let [$(vVariableName)] = Peek('Expression',vI,'Variables');

Next

Doing so, you can use dollar sign expansions etc. in your excel columns with the need for a special treatment (except maybe a leading single quote before potential equal signs to avoid Excel evaluating your expressions...)