Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chrisellis
Contributor

Find Top n Record per group

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

1 Reply
chrisellis
Contributor

Re: Find Top n Record per group

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

Community Browser