Aggregating top 5 values

[eduardo_dimperio]

Hi,

I have some telecom operators and i need to get the nearest 3 of each

DISTINCT

ID_MODULE, //ID TO SELECT

CODIGO_OPERADORA_TORRE_INATIVA AS CODIGO_OPERADORA, //OPERATOR CODE

Distancia_Torre_Inativa // Distance

RESIDENT TORRES_INATIVAS

I thought to use Min(Distancia_Torre_Inativa ) Group by CODIGO_OPERADORA_TORRE_INATIVA, but this will bring me 1 value.

So, Each ID_MODULE has a number of CODIGO_OPERADORA_TORRE_INATIVA  with a distance (Distancia_Torre_Inativa ).

Input:

ID MODULE   Operator       Distance   

12345               A                    3

54821               B                    8

55154               A                    2

55866               C                    1

15545               A                    5

57781               B                    7

59994               A                    9

43866               C                    6

78821               B                    8

92154               A                    2

44866               C                    1

50545               A                    2

78981               B                    7

51794               A                    8

36866               C                    6

Output:

ID MODULE   Operator       Distance 

55154               A                    2

92154               A                    2

50545               A                    2

78981               B                    7

57781               B                    7

54821               B                    8

55866               C                    1

44866               C                    1

43866               C                    6

[tajmohamed30]

Rank function in expression

[antoniotiman]

Hi Eduardo,

Expression :

If(Rank(-Distance,4,1) <= 3,Distance)

Regards,

Antonio

[Kushal_Chawda]

Another solution

Data:

LOAD * Inline [

ID MODULE,  Operator,      Distance 

12345,              A ,                  3

54821 ,              B ,                  8

55154 ,              A,                    2

55866,              C,                    1

15545,              A,                    5

57781,              B,                    7

59994,              A,                    9

43866,              C ,                  6

78821,              B,                    8

92154,              A,                    2

44866,              C ,                  1

50545,              A,                    2

78981,              B,                    7

51794,              A,                    8

36866,              C ,                  6 ] ;

New:

LOAD *,

        AutoNumber(RowNo(),Operator) as Rank

Resident Data

Order by Operator,Distance;

DROP Table Data;

Expression:

=sum({<Rank={"<=3"}>}Distance)

[eduardo_dimperio]

Hi antonio !

Rank is for Chart and i need to script

[eduardo_dimperio]

Hi Kushal !

Like Antonio this is a Chart solucion, Rank doesn't work to script and im working with maps so a chat solution will keep my maps load to slow and consuming too much memory.

[Kushal_Chawda · Accepted solution]

try this

Data:

LOAD * Inline [

ID MODULE,  Operator,      Distance 

12345,              A ,                  3

54821 ,              B ,                  8

55154 ,              A,                    2

55866,              C,                    1

15545,              A,                    5

57781,              B,                    7

59994,              A,                    9

43866,              C ,                  6

78821,              B,                    8

92154,              A,                    2

44866,              C ,                  1

50545,              A,                    2

78981,              B,                    7

51794,              A,                    8

36866,              C ,                  6 ] ;

New:

LOAD *,

        AutoNumber(RowNo(),Operator) as Rank

Resident Data

Order by Operator,Distance;

DROP Table Data;

Final:

noconcatenate

LAD *

resident New

where Rank<=3;

drop table New;

[eduardo_dimperio]

Hi Kushal, that was very nice solution

Thank you !

Now please help me with javascript in my other post lol