7 Replies Latest reply: Jun 13, 2017 9:04 AM by Eduardo DImperio RSS

    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

       

       

        • Re: Aggregating top 5 values
          Antonio Mancini

          Hi Eduardo,

          Expression :

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

           

          Regards,

          Antonio

           

          • Re: Aggregating top 5 values
            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)

             

             

              • Re: Aggregating top 5 values
                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.

                  • Re: Aggregating top 5 values
                    kushal chawda

                    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;