Top1 Rank for each dept and year

qlikviewwizard — Thu, 30 Jul 2015 01:58:37 GMT

Hi All,

How to show the top1 Rank for each dept and year?

Thanks in advance.

script:

SET DateFormat='DD-MMM-YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

EMPLOYEE:

LOAD * , year(date(DATE)) as YEAR;

LOAD EMPNO,ENAME,JOB,MGR,date(DATE) as DATE,SAL,DEPTNO INLINE [

EMPNO,ENAME,JOB,MGR,DATE,SAL,DEPTNO

7369,SMITH,CLERK,7902,01-Jan-2015 ,800,20

7499,ALLEN,SALESMAN,7698,01-Jan-2015 ,1600,30

7521,WARD,SALESMAN,7698,01-Feb-2015 ,1250,30

7566,JONES,MANAGER,7839,01-Feb-2015 ,2975,20

7654,MARTIN,SALESMAN,7698,01-Mar-2015 ,1250,30

7698,BLAKE,MANAGER,7839,01-Mar-2015 ,2850,30

7782,CLARK,MANAGER,7839,01-Apr-2015 ,2450,10

7788,SCOTT,ANALYST,7566,01-Apr-2015 ,3000,20

7839,KING,PRESIDENT,NULL,01-Apr-2015 ,5000,10

7844,TURNER,SALESMAN,7698,01-May-2015 ,1500,30

7876,ADAMS,CLERK,7788,01-Jun-2015 ,1100,20

7900,JAMES,CLERK,7698,01-Jun-2015 ,3000,30

7902,FORD,ANALYST,7566,01-Jul-2015 ,5000,20

7934,MILLER,CLERK,7782,01-Jul-2015 ,2975,10

7369,SMITH,CLERK,7902,01-Jan-2014 ,800,20

7499,ALLEN,SALESMAN,7698,01-Jan-2014 ,3000,30

7521,WARD,SALESMAN,7698,01-Feb-2014 ,5000,30

7566,JONES,MANAGER,7839,01-Feb-2014 ,2975,20

7654,MARTIN,SALESMAN,7698,01-Mar-2014 ,3000,30

7698,BLAKE,MANAGER,7839,01-Mar-2014 ,5000,30

7782,CLARK,MANAGER,7839,01-Apr-2014 ,2975,10

7788,SCOTT,ANALYST,7566,01-Apr-2014 ,3000,20

7839,KING,PRESIDENT,NULL,01-Apr-2014 ,5000,10

7844,TURNER,SALESMAN,7698,01-May-2014 ,1500,30

7876,ADAMS,CLERK,7788,01-Jun-2014 ,3000,20

7900,JAMES,CLERK,7698,01-Jun-2014 ,5000,30

7902,FORD,ANALYST,7566,01-Jul-2014 ,2975,20

7934,MILLER,CLERK,7782,01-Jul-2014 ,1300,10

];

Re: Top1 Rank for each dept and year

Gysbert_Wassenaar — Thu, 30 Jul 2015 05:12:11 GMT

See attached qvw.