Creating Rankings in Load Script

[#1]

Hi all,

I was hoping the Community would be able to help me address the following issue:

I want to generate a ranking for each individual via script, so that his/her ranking withing his role (aka #3 in Sales, or #3 in Management, etc).

Given 3 main fields - EMP_ID, ROLE, SCORE.

I would like to create a rank within the ROLE for each employee (EMP_ID), based on SCORE, so that each ROLE has its own ranking.

Using set analysis, I was actually able to accomplish something close to what I need. Works well when the user is an Admin and has access to the entire dataset.

However, entitlements are in place to limit each user access to only the information relevant to their own ID. Therefore, from my Admin point of view I can see John Smith as #22 of 115 in Sales. John Smith, however, currently sees that he is #1 of 1 in Sales, as the rest of the dataset is not available to him.

I would like to have the #22 (in this case) to be associated with every row for John Smith, so that, regardless of his access, I can show that he is ranked number 22 in his ROLE.

Thank you very much in advance. And please let me know if you would like me to elaborate some more.

[swuehl · Accepted solution]

Maybe like

INPUT:

LOAD EMP_ID, ROLE, SCORE

FROM YourSourceTable;

RESULT:

LOAD EMP_ID, ROLE, SCORE, Autonumber(recno() , ROLE) as RANK

RESIDENT INPUT

ORDER BY ROLE, SCORE desc;

DROP TABLE INPUT;

edit:

If your source system for YourSourceTable supports ORDER BY clause, you can also do it in one LOAD statement.