topic Group by in QlikView

Group by

Anonymous — Fri, 01 Nov 2013 17:22:16 GMT

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 — Fri, 01 Nov 2013 18:00:14 GMT

See attached qvw.

Anonymous — Sat, 02 Nov 2013 01:29:18 GMT

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 — Sat, 02 Nov 2013 06:31:41 GMT

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

Anonymous — Sat, 02 Nov 2013 07:59:34 GMT

Funny I did same in the script but hey thanks