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

[suzel404]

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 ?

[swuehl · Accepted solution]

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

[rwunderlich]

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

[suzel404]

Thank you Rob.

[suzel404]

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.

[swuehl]

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

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

[suzel404]

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