Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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?

Thanks in advance!

GPC

Tags (5)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Top 3 x for each y in load script


Hi,

Try this script

 

Temp:

 

LOAD

*

 

INLINE

[

 

Data:

 

LOAD

 

*

 

WHERE

Rank

<=3

;

 

LOAD

 

*

,

 

If

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

Rank

 

RESIDENT

Temp

 

ORDER

By

Y

,X

Desc;

 

DROP

TABLE

Temp;

Regards,

Jagan.

View solution in original post

4 Replies
mov
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

manas_bn
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:

LOAD *

,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)

LOAD

ID

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

RESIDENT Fact

ORDER BY Exclusive,Project, Bid DESC

Result:

Capture.PNG.png

MVP & Luminary
MVP & Luminary

Re: Top 3 x for each y in load script


Hi,

Try this script

 

Temp:

 

LOAD

*

 

INLINE

[

 

Data:

 

LOAD

 

*

 

WHERE

Rank

<=3

;

 

LOAD

 

*

,

 

If

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

Rank

 

RESIDENT

Temp

 

ORDER

By

Y

,X

Desc;

 

DROP

TABLE

Temp;

Regards,

Jagan.

View solution in original post

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