topic Top 3 x for each y in load script in QlikView

Top 3 x for each y in load script

Thu, 26 Sep 2013 15:10:14 GMT

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:

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

Re: Top 3 x for each y in load script

Anonymous — Thu, 26 Sep 2013 15:39:41 GMT

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

Re: Top 3 x for each y in load script

manas_bn — Fri, 27 Sep 2013 05:20:17 GMT

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

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

RESIDENT Fact

ORDER BY Exclusive,Project, Bid DESC

Result:

Re: Top 3 x for each y in load script

jagan — Fri, 27 Sep 2013 05:36:08 GMT

Hi,

Try this script

Temp:

LOAD

INLINE

[

Data:

LOAD

WHERE

Rank

<=3

;

LOAD

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

Rank

RESIDENT

Temp

ORDER

Desc;

DROP

TABLE

Temp;

Regards,

Jagan.

Re: Top 3 x for each y in load script

Fri, 27 Sep 2013 09:23:18 GMT

Brilliant! Thanks.

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

GPC