Top n records of dimension parts

[#1]

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

[Anonymous · Accepted solution]

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;

[johnw]

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

;