# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Highlighted
Not applicable

## Top 3 x for each y in load script

Hi All,

I have to pull out the top 3 x's for each y based on a frequency field during the load script.

My data is in the following format:

X

Y

Frequency_field

I have been playing around with recno() but can't get it to restart for each X... Does anyone have any elegant solutions to this problem?

GPC

Tags (5)
1 Solution

Accepted Solutions
MVP & Luminary

## Re: Top 3 x for each y in load script

Hi,

Try this script

Temp:

*

INLINE

[

Data:

*

WHERE

Rank

<=3

;

*

,

If

(Previous(Y) <> Y, 1, Peek('Rank') + 1) AS

Rank

RESIDENT

Temp

ORDER

By

Y

,X

Desc;

DROP

TABLE

Temp;

Regards,

Jagan.

4 Replies
Esteemed Contributor III

## Re: Top 3 x for each y in load script

Do you need the numbers?  What about a loop for all Y, and in each loop use "first 3", and order by Frequency_field desc.

Regards,

Michael

Contributor

## Re: Top 3 x for each y in load script

Hi Gareth,

Is the below something that you are looking for? It ranks every Project based on exclusivity. So you can use set analysis to get the top N Projects

sum({\$<Rank={"<=3"}>} Bid)

Fact:

,RecNo() as ID                                     // Create ID Field

INLINE [

Customer, Project, Exclusive, Bid

A, foo, 1, 40

B, foo, 0, 1

C, foo, 0, 2

D, foo, 0, 4

E, foo, 0, 7

F, foo, 0, 10

A, bar, 0, 10

B, bar, 0, 4

A, ded, 1, 30

B, ded, 1, 15

C, ded, 0, 5

D, ded, 0, 5

];

// Join Rank to Fact table

LEFT JOIN (Fact)

ID

,if(Project=Previous(Project),peek(Rank)+1,1) as Rank

RESIDENT Fact

ORDER BY Exclusive,Project, Bid DESC

Result:

MVP & Luminary

## Re: Top 3 x for each y in load script

Hi,

Try this script

Temp:

*

INLINE

[

Data:

*

WHERE

Rank

<=3

;

*

,

If

(Previous(Y) <> Y, 1, Peek('Rank') + 1) AS

Rank

RESIDENT

Temp

ORDER

By

Y

,X

Desc;

DROP

TABLE

Temp;

Regards,

Jagan.

Not applicable

## Re: Top 3 x for each y in load script

Brilliant!  Thanks.

I have never seen the peek and previous functions used in that way in a load - very clever.

GPC