Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Given below statement is working fine when I talking about top 10
First 10
Load * from abc.csv
order by sal desc;
My question is how I do to get Top 10 sal by each department (in script level only), suppose if I have 4 departments so I will get 40 records means top 10 records for each department.
Regards,
Zain.
Hi,
You can't do order on file loads. But you can when the file has been loaded.
This should work:
abc_tmp:
Load * from abc.csv;
abc:
NoConcatenate
Load * Resident abc_tmp
order by sal desc;
DROP Table abc_tmp;
This might also work:
abc:
First 10
Load *
order by sal desc;
Load * from abc.csv;
Thanks, Johan for your reply,
I know very well which you mentioned in your solution,
I want the top n group by department mean top n of each department.
Regards,
Zain.
Hi, sorry for not reading prooperly, you need to do this in two steps: 1 rank the records and 2, filter out the ranked lines. It should go something like this (not tested)
abc_tmp:
Load * from abc.csv;
abc_tmp2:
NoConcatenate
Load *,
If (Department = Peek (Department),
RangeSum (1, Peek (Rank)),
1) as Rank
Resident abc_tmp
Order By Department, sal desc;
DROP Table abc_tmp;
abc:
NoConcatenate
LOAD *
Resident abc_tmp2
Where Rank <= 10;
DROP Table abc_tmp2;
That should do it.
thanks, I will check on Monday and then give you feedback on this regard.
Zain.