topic Sum in Script with Set Analysis in QlikView

Sum in Script with Set Analysis

Sun, 27 Nov 2011 11:19:11 GMT

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

Sum in Script with Set Analysis

lironbaram — Sun, 27 Nov 2011 11:37:54 GMT

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

Sum in Script with Set Analysis

Sun, 27 Nov 2011 12:03:57 GMT

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

Sum in Script with Set Analysis

lironbaram — Sun, 27 Nov 2011 12:41:10 GMT

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;

Sum in Script with Set Analysis

Sun, 27 Nov 2011 15:42:20 GMT

Perfect! Thank you