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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni


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
Anonymous
Not applicable
Author

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
Creator
Creator

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

jagan
Luminary Alumni
Luminary Alumni


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.

Not applicable
Author

Brilliant!  Thanks.

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

GPC