4 Replies Latest reply: Nov 27, 2011 10:42 AM by the fourth

# Sum in Script with Set Analysis

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

Data:

*

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

• ###### Sum in Script with Set Analysis

hei

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

try this  script

Data:

*

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)';

• ###### Sum in Script with Set Analysis

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:

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

• ###### Sum in Script with Set Analysis

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: