Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (1)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Top n records of dimension parts

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;

2 Replies
mov
Esteemed Contributor III

Top n records of dimension parts

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;

MVP
MVP

Re: Top n records of dimension parts

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

;

Community Browser