1 Reply Latest reply: Jul 11, 2016 9:01 AM by Christopher Ellis RSS

    Find Top n Record per group

    Christopher Ellis

      Is it possible to use a loop to look at the records with the same primary key by putting a loop in the middle of a load.

       

      The idea is to order the primary key and check to see if it changes, if it does get the next 3 records for that primary key and so on.

       

      The syntax isn't quit right but the principle hopefully show what I am trying to do.

       

      TableA:
      noconcatenate
      LOAD
          PrimaryKey,  
          Result
              Do While (Previous(Primary)=Primary)

                 First 3,

                 Result As B1

              Loop

      Resident TableB
      Order By Primary;

       

      Any help would be appreciated.

       

      Thanks

       

      Chris

        • Re: Find Top n Record per group
          Christopher Ellis

          Having a look around this may work better but I cant seem to get the if statement syntax correct.

           

          Could anyone help.

           

          LET TotalRecords=NoOfRows('TableA'); //get total rows from TableA

          for i=1 to $(TotalRecords) // loop through every row in TableA
             TableB:

              First  3
              LOAD
                   Primary
                        if Previous(Primary)=Primary then

                              Result As Top3
                        End if
              Resident TableA
              Order By Primary;
          Next

           

          Thanks

           

          Chris