Qlik Community

Qlik Deployment Framework

This group hosts information related to the Qlik Deployment Framework (QDF). Providing best practices, libraries and utilities that facilitate the recommended setup and management of QlikView or Qlik Sense environments. An understanding of the Qlik platform is recommended before joining this group. Download QDF and documentation here: https://github.com/QlikDeploymentFramework/Qlik-Deployment-Framework/releases

Highlighted
zain16nib
New Contributor III

Top 10 Records of Each Department

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.

Tags (1)
5 Replies
techvarun
Valued Contributor II

Re: Top 10 Records of Each Department

Partner
Partner

Re: Top 10 Records of Each Department

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;

zain16nib
New Contributor III

Re: Top 10 Records of Each Department

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.

Partner
Partner

Re: Top 10 Records of Each Department

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.

zain16nib
New Contributor III

Re: Top 10 Records of Each Department

thanks, I will check on Monday and then give you feedback on this regard.

Zain.