Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Top n records of dimension parts

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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;

View solution in original post

2 Replies
Anonymous
Not applicable
Author

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
Champion III
Champion III

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

;