topic Re: Set Analysis with Only function doesn't work in script. in QlikView

Set Analysis with Only function doesn't work in script.

suzel404 — Sun, 25 May 2014 08:54:35 GMT

Hi all,

I have a simple range table with amount and start range and End range.

When I select a range I want the range-1 and the range+1 given by the Id.

I use four varibles but my syntax works only in script :

LET vUpStartEnd = 'Only({<Id={$(=Id+1)}, StartEnd=>} StartEnd)';
LET vDownStartEnd = 'Only({<Id={$(=Id-1)}, StartEnd=>} StartEnd)';
LET vUpAmount = 'Only({<Id={$(=Id+1)}, StartEnd=>} Amount)';
LET vDownAmount = 'Only({<Id={$(=Id-1)}, StartEnd=>} Amount)';
Data:
LOAD
*,
'$(vUpStartEnd)' as UpStartEnd,
'$(vDownStartEnd)' as DownStartEnd,
'$(vUpAmount)' as UpAmount,
'$(vDownAmount)' as DownAmount
RESIDENT Data_Tmp;

I would like one table with my 4 varaibles. How can I achieve that ?

Re: Set Analysis with Only function doesn't work in script.

swuehl — Sun, 25 May 2014 12:40:47 GMT

You can't use set analysis in the script. But you can use peek() or previous() functions to access a preceding record's values, see attached sample.

If you want to define a variable in the script, take care that QV will (try to) evaluate the dollar sign expansion before the variable value is assigned, that's why this doesn't work in your example.

Variable interpreted in script

Re: Set Analysis with Only function doesn't work in script.

rwunderlich — Sun, 25 May 2014 14:34:14 GMT

You'll need to escape the $ in your script variable definitions to prevent them from getting expanded in the script. One way to do this is with the replace() function.

LET vUpStartEnd = replace('Only({<Id={@(=Id+1)}, StartEnd=>} StartEnd)','@','$');

See attached.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Set Analysis with Only function doesn't work in script.

suzel404 — Sun, 25 May 2014 21:12:04 GMT

Thank you Rob.

Re: Re: Set Analysis with Only function doesn't work in script.

suzel404 — Sun, 25 May 2014 21:24:51 GMT

Swehl,

I reproduce the syntax on the orginal table but I don't have the previous and next range.

For example, I select the chronotype =Type1 and the agglomerationType =TypeA

I can see the StartEnd. In particular I select the startEnd (2000-2500) and I can see previous (1500-2000) and forward StartEnd (2500-3250).

I need to create Peek function for StartEnd,Start,End,Money,Amount fields.

Previous/Forward : StartEnd,Start,End,Money,Amount.

When I create Peek Function, I see duplicate Id. The Id field is unique and identifie Start, End, StartEnd, Money, Amount.

Thank you for you help.

Re: Set Analysis with Only function doesn't work in script.

swuehl — Sun, 25 May 2014 21:50:23 GMT

Looking into your Excel source table, Id is not unique.

Peek() function as shown in my original sample won't duplicate your Id here.

Re: Set Analysis with Only function doesn't work in script.

suzel404 — Sun, 25 May 2014 22:52:52 GMT

Thank you, Swehl. I didn't see that Id was duplicated. It is due to the intervalMatch.