2 Replies Latest reply: Nov 16, 2011 4:49 PM by John Witherspoon RSS

    Top n records of dimension parts

    Przemysław Wojda

      Hi All, I'm looking for a script function that will let me top n records of given dimension parts e.g. from the below set I'd like to load only John 54, John 21 and Mary 17, Mary 15.

       

      John, 54

      Mary, 12

      Mary, 17

      John, 21,

      John, 11

      Mary, 15,

      John, 7

       

      Thanks,

      Przemek

        • Top n records of dimension parts
          Michael Solomovich

          Well, this is an example.  John W will argue again about the loop - but if you don't need top 100,000 you'll be fine :-)

           

          data:
          LOAD *INLINE [
          Name, Age
          John, 54
          Mary, 12
          Mary, 17
          John, 21,
          John, 11
          Mary, 15,
          John, 7];

           

          FOR n=1 to 2


          table:
          LOAD
          Name,
          max(Age,$(n)) as TopAge
          RESIDENT data
          GROUP BY Name;

           

          NEXT n
          Drop Table data;

           

            • Re: Top n records of dimension parts
              John Witherspoon

              Michael Solomovich wrote:

               

              John W will argue again about the loop - but if you don't need top 100,000 you'll be fine :-)

               


              Right on both counts!  I will argue that a loop is inefficient, but you're right that for low values of N you'll be fine. :-)

               

              If I'm going to argue, though, I have to provide a non-looping alternative.  Here's one.  Script below, example attached.

               

              Data:

              LOAD *

              ,recno() as ID

              INLINE [

              Name, Value

              John, 54

              Mary, 12

              Mary, 17

              John, 21,

              John, 11

              Mary, 15,

              John, 7

              ];

              LEFT JOIN (Data)

              LOAD

              ID

              ,if(Name=previous(Name),peek(Count)+1,1) as Count

              RESIDENT Data

              ORDER BY Name, Value DESC

              ;

              INNER JOIN (Data)

              LOAD recno() as Count

              AUTOGENERATE 2 // This is your N records.

              ;

              DROP FIELDS

              Count

              ,ID

              ;