topic Re: Variable interpreted in script in QlikView

Variable interpreted in script

Mon, 07 Oct 2013 10:43:40 GMT

Hello,

I have the following Set Analysis expression which I store in a variable in the script (it is not supposed to be modified so I do not want it defined only in Variable Overview):

SET SetAnalysis = Count({$<CODE = {'LAST'}, DATE = {">= $(=Min(CalendarDate))"} * {"<= $(=Max(CalendarDate))"}>} DISTINCT ISSUE]);

The issue is that when the script runs, it tries to interpret $(=Min(CalendarDate)) and $(=Max(CalendarDate)) despite the SET but Min() and Max() do not work in script so it fails (Graph expression only).

How can I make sure script execution does not try to intepret $(=Min(CalendarDate)) and $(=Max(CalendarDate)) (and fail).

I have tried a few modifications without success to make it look like a string:

SET SetAnalysis = Count({$<CODE = {'LAST'}, DATE = {">= $(=Min(CalendarDate))"} * {"<= $(=Max(CalendarDate))"}>} DISTINCT ISSUE]);
SET SetAnalysis1 = 'Count({$<CODE = {"LAST"}, DATE = {">= $(=Min(CalendarDate))"} * {"<= $(=Max(CalendarDate))"}>} DISTINCT ISSUE])';
SET SetAnalysis2 = "Count({$<CODE = {'LAST'}, DATE = {'>= $(=Min(CalendarDate))'} * {'<= $(=Max(CalendarDate))'}>} DISTINCT ISSUE])";

Is that possible?

Re: Variable interpreted in script

jjordaan — Mon, 07 Oct 2013 10:49:33 GMT

Hi cc cc,

You should place the code between ''.

Re: Variable interpreted in script

swuehl — Mon, 07 Oct 2013 10:50:06 GMT

You need to prevent QV from evaluating the dollar sign expansion, either by doing something like

LET SetAnalysis = Count({$<CODE = {'LAST'}, DATE = {">= ' & '$' & '(=Min(CalendarDate))"} * {"<= ' & '$' & '(=Max(CalendarDate))"}>} DISTINCT ISSUE]);

Or substituiting the $ char with some other, e.g. @ and doing a replace on your variable in a second statement.

Re: Variable interpreted in script

israrkhan — Mon, 07 Oct 2013 10:52:31 GMT

have you tried " " around your expression like below:

SET SetAnalysis = "Count({$<CODE = {'LAST'}, DATE = {">= $(=Min(CalendarDate))"} * {"<= $(=Max(CalendarDate))"}>} DISTINCT ISSUE]);"

i think it will consider it a whole string and will not try to calculate expression.

i did not tried but seems to work..

Khan

Re: Re: Variable interpreted in script

Mon, 07 Oct 2013 11:32:00 GMT

@Jeoren:

I tried

SET SetAnalysis2 = "Count({$<CODE = {'LAST'}, DATE = {'>= $(=Min(CalendarDate))'} * {'<= $(=Max(CalendarDate))'}>} DISTINCT ISSUE])";

It fails:

Count({$<CODE = {'LAST'}, DATE = {'>= (internal error)'} * {'<= (internal error)'}>} DISTINCT ISSUE)

Re: Variable interpreted in script

jjordaan — Mon, 07 Oct 2013 11:40:55 GMT

I mean single quotes

SET SetAnalysis2 = 'Count({$<CODE = {'LAST'}, DATE = {'>= $(=Min(CalendarDate))'} * {'<= $(=Max(CalendarDate))'}>} DISTINCT ISSUE])';

Re: Re: Variable interpreted in script

Mon, 07 Oct 2013 11:42:03 GMT

I tried the following:

LET SetAnalysis = "Count({$<CODE = {'LAST'}, DATE = {'>= ' & '$' & '(=Min(CalendarDate))'} * {'<= ' & '$' & '(=Max(CalendarDate))'}>} DISTINCT ISSUE])";

Added double quotes around so it does not interpret Count() and replaced all others by single quotes but when I check SetAnalysis after execution, it is empty so I guess something goes wrong.

Re: Re: Variable interpreted in script

Mon, 07 Oct 2013 11:50:33 GMT

Did not work

Variable Overview:

'Count({$<CODE = {'LAST'}, DATE = {'>= (internal error)'} * {'<= (internal error)'}>} DISTINCT ISSUE)'

I did not expect the parser to be able to handle opening/closing quotes but it looks like it can.

I do not know difference between ' and " (apart from syntax highlighting) and if it makes a real difference compared to PHP or other languages.

Re: Re: Variable interpreted in script

swuehl — Mon, 07 Oct 2013 11:52:30 GMT

This one works for me ( you need to handle also the quoted LAST field modifer value):

LET SetAnalysis = '=Count({$<CODE = {'&chr(39)&'LAST'&chr(39)&'}, DATE = {">= ' & '$' & '(=Min(CalendarDate))<= ' & '$' & '(=Max(CalendarDate))"}>} DISTINCT ISSUE])';

edit: Or use the replace method:

SET SetAnalysisTmp = Count({$<CODE = {'LAST'}, DATE = {">=@(=Min(CalendarDate))<=@(=Max(CalendarDate))"}>} DISTINCT ISSUE]);

LET SetAnalysis2 = replace('$(SetAnalysisTmp)','@','$');

Re: Variable interpreted in script

tresB — Mon, 07 Oct 2013 12:01:17 GMT

You have to use Replace() intelligently, I am just copying one section of discussion from here by @jacob drummond. I am sure this would help you.

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],'~','$')";

Re: Re: Variable interpreted in script

jjordaan — Mon, 07 Oct 2013 14:05:34 GMT

This is working I my script.

SET SetAnalysis2 = 'Count({$<CODE = {"LAST"}, DATE = {">= $(=Min(CalendarDate))"} * {"<= $(=Max(CalendarDate))"}>} DISTINCT ISSUE])';

Can you send me a example zo I can look into it?

Re: Re: Re: Variable interpreted in script

Mon, 07 Oct 2013 18:07:01 GMT

Here is an example. Data do not make much sense, it is just for test purpose. You basically are supposed to Sum COST for Products 3 to 8 (= 10).

swuehl proposal works fine. I initially switched some '/" to his solution because I did not expect DATE = {">= ' to be properly parsed but in fact it does.

I was hoping for a lighter/cleaner version but I guess I do not have a choice.

Since those Set Analysis expressions are not supposed to be modified, I find it cleaner to declare them in the script instead of the Variable Overview where I would only store variables meant to be modified and retrieved from one session to another.

Re: Variable interpreted in script

flipside — Tue, 08 Oct 2013 10:15:46 GMT

Although I haven't fully tested it, one solution which seems to work with your example is to replace the code you need to defer dollar expansion with a parameter holder and then submit the code at run time. Using your sample, here's how it works ...

Your target code is ...

Set SAflipside = Sum({$<CODE={"Y"}, PRODUCT = {">= $(=Min(MIN))"} * {"<= $(=Max(MAX))"}>} SOLD)

... but you need to defer the Min and Max calculations. Replace ALL the PRODUCT code restriction with a holder ($1) so it becomes ...

Set SAflipside = Sum({$<CODE={"Y"}, PRODUCT = $1>} SOLD) - it will show an error in the variable list (red underline), but it works.

... then in your front end object, resubmit it as this, the $1 holder is replaced by the submitted code in bold ...

$(SAflipside({">= $(=Min(MIN))"} * {"<= $(=Max(MAX))"}))

flipside

Re: Variable interpreted in script

Tue, 08 Oct 2013 16:20:23 GMT

Thanks flipside but it is important to me to only have one variable call in the front end objects.

In case you need change a field name or adjust a formula, updating it everywhere is too painfull.

But it looked like a good trick!

Re: Variable interpreted in script

flipside — Wed, 09 Oct 2013 08:22:28 GMT

Technically it is still only one variable call and refining it the following way may be of more interest as you can control the parameters from within the script to make global changes easier.

SET SAflipside3 = Sum({$<CODE={"Y"}, PRODUCT = {'>=$1'} * {'<=$2'}>} SOLD);
SET p1= =Min(MIN);
SET p2= =Max(MAX);

then use the call as ...

$(SAflipside3($(p1),$(p2)))

flipside

Re: Variable interpreted in script

Wed, 16 Oct 2013 10:16:41 GMT

It works perfectly flipside.

swuehl solution works fine too but I find the variable call more elegant from a code a point of view.

Thanks a lot guys,