Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
jagan
Not applicable

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.

4 Replies
mov
Not applicable

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

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

jagan
Not applicable

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.

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