Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi Eduardo,
Expression :
If(Rank(-Distance,4,1) <= 3,Distance)
Regards,
Antonio
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)
Hi antonio !
Rank is for Chart and i need to script
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.
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;
Hi Kushal, that was very nice solution
Thank you !
Now please help me with javascript in my other post lol