Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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:
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.
Brilliant! Thanks.
I have never seen the peek and previous functions used in that way in a load - very clever.
GPC