topic Re: Can I SET or LET a variable to include a $(=...) in QlikView

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

Tue, 05 Jan 2016 04:08:05 GMT

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.

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

tresB — Tue, 05 Jan 2016 07:53:26 GMT

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

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

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

tamilarasu — Tue, 05 Jan 2016 08:22:31 GMT

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)', '@' , '$');

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

Peter_Cammaert — Tue, 05 Jan 2016 08:33:47 GMT

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.

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

Tue, 05 Jan 2016 15:50:35 GMT

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!

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

Fri, 08 Jan 2016 22:26:38 GMT

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, ‘¬’, ‘$’);

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

Fri, 08 Jan 2016 22:27:04 GMT

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

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

Peter_Cammaert — Sat, 09 Jan 2016 15:59:09 GMT

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

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

Sat, 09 Jan 2016 18:29:04 GMT

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

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

swuehl — Mon, 11 Jan 2016 11:00:12 GMT

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...)

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

Mon, 11 Jan 2016 17:29:12 GMT

Oooh, another clever suggestion, I didn't think of that.

I really like it because I can

Maintain variables in plain text (all of these answers support that (a QVS supports that)-- but swuehl's suggestion forces you to separate variable definitions from other LOAD -- for better or worse [I think it's for better])
I can use dollar-signs without "workarounds" like substitution or string concatenation. Therefore my variable is uninterrupted and easier to read and write.
AND bonus: I could edit it in Excel or some CSV editor so I get the look-and-feel of the variable UI in Qlikview (Ctrl+Alt+V), and the advantage of extra columns (i.e. a "Comment" column)

But you said:

Doing so, you can use dollar sign expansions etc. in your excel columns with the need for a special treatment

Did you mean:

without the need for special treatment

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

swuehl — Mon, 11 Jan 2016 20:59:05 GMT

Yes!