Sum in Script with Set Analysis

[#1]

Hi, i don't understand why if i have this script:

Data:

Load

*

Inline [

    Quantity, REGION, ID

    2 , A , 1

    2 , A , 2

    2 , A , 3

    2 , B , 4

    2 , B , 5

];

Let vFilter = '=maxstring(REGION)';

Let vSum1 = '=Sum({$<REGION = {"=$(vFilter)"}>} Quantity)';

Let vSum2 = '=Sum({$<REGION = {B}>} Quantity)';

vSum1 = 0

but

vSum2 = 4

is there any way to do the Sum using the result of variable vFilter?

p.d. I know it is possible using "Group By", but this time i need it using like explain

Thanks in advance

[lironbaram]

hei

the problem you have is that the second varaible implent the first one as is writen

try this  script

Data:

Load

*

Inline [

Quantity, REGION, ID
2 , A , 1
2 , A , 2
2 , A , 3
2 , B , 4
2 , B , 5
];



Let vFilter ='=maxstring(REGION)';

Let vSum1 ='= Sum({$<REGION = {"' & chr(36) &'(vFilter)"}>} Quantity)';

[#3]

Ok, its correct but now i have other problem, i need to filter the Data table by the maxstring of REGION using a variable. I try this:

Let vRegion= '=maxstring(REGION)';

Data2:

Load Quantity as Q,

REGION as R,

ID as I

resident Data

where REGION = '=' & chr(36) &'(vRegion))'

;

drop table Data;

rename fields Q to Quantity, R to REGION, I to ID;

but it doesn't work as expect, all the fields dissapear and the variables are not filled.

Do you know why?

Thanks

[lironbaram · Accepted solution]

now you want to filter the data on the fly in the load script

so you have to use group by

use this script to put value in to the new varaible

maxregion:

load maxstring(REGION) as MaxRegion
Resident Data;
Let vRegion = peek('MaxRegion',0,'maxregion');

drop Table maxregion;

[#5]

Perfect! Thank you