Discussion Board for collaboration on QlikView Scripting.
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:
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!
Go to Solution.
Try this script
(Previous(Y) <> Y, 1, Peek('Rank') + 1) AS
View solution in original post
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.
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
,RecNo() as ID // Create ID Field
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)
,if(Project=Previous(Project),peek(Rank)+1,1) as Rank
ORDER BY Exclusive,Project, Bid DESC
I have never seen the peek and previous functions used in that way in a load - very clever.