Group by

[Anonymous]

Header 1	Header 2	Header 3	Header 4	Header 5
ID	COUNT	LOCATION	SIGR	VALUE
AA23	01	UK	10000	45000
AA23	02	UK	15000	56000
AA23	03	UK	500	65000

Hi ,

I want create a range from sigr where the Count = Max(count) . Now with the condition satisfied  I would have something like this ;

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
RANGE FIELD	ID	COUNT	LOCATION	SIGR	VALUE
A	AA23	03	UK	500	65000

But actually I would want the summation of all the figures in the value field for  the range band and the script below is not giving me the result.

Temp_Table:

Load * from Temp_Table;

Left join(Temp_Table)

Load ID,

Max(Count) as CountTemp

sum(Value)

resident Temp_Table group by ID;

Load *,

IF(SIGR <= 99999 AND COUNT = CountTemp  ,'A',

IF( SIGR > 99999 AND  SIGR <= 49999 AND COUNT=CountTemp ,'B' )) as RangeField

resident Temp_Table;

Drop table Temp_Table;

Drop field CountTemp;

[Gysbert_Wassenaar]

See attached qvw.

[Anonymous]

Hi @gwassenaar

Thanks for that but i had wanted the VALUE2 to be the summation of all numbers in VALUE FIELD

eg. VALUE2 =45000 + 56000 +65000 = 168000

Thanks

[Gysbert_Wassenaar · Accepted solution]

Replace FirstSortedValue(VALUE,-COUNT) as VALUE_2 wtih sum(VALUE) as VALUE_2

[Anonymous]

Funny I did same in the script but hey thanks