topic Top n records of dimension parts in QlikView

Top n records of dimension parts

Wed, 16 Nov 2011 20:54:10 GMT

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

Top n records of dimension parts

Anonymous — Wed, 16 Nov 2011 21:24:57 GMT

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;

Re: Top n records of dimension parts

johnw — Wed, 16 Nov 2011 21:49:12 GMT

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

,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

;