Calculated Dimension Or Expression needed to Rank data

[#1]

HI I am a new user of Qlikview so any help provided is greatly appreciated.

I am trying to apply a ranking to my source data and I am struggling to get it to work.

This is my base data

Source Data
Request Id	Rank Role	Task Completed By Colleague Name	Request Status	Credit Line Amt	Customer
111222	CA-1	John Smith	More info needed	100,000	ABC Ltd
111222	CA-2	Simon Sun	More info provided	100,000	ABC Ltd
111222	CA-3	Lisa Marie	Approved	100,000	ABC Ltd
111333	CA-1	Tony Low	Recommended for approval	500,000	BIC Ltd
111333	CA-2	Mary Night	Recommended for apporval	500,000	BIC Ltd
111333	CA-3	Brian McNight	Approved	500,000	BIC Ltd
111444	CA-1	John Smith	Recommended for approval	1,000,000	Higher Ltd
111444	CM-1	Shane Hollow	Declined	1,000,000	Higher Ltd
111555	CA-1	Mary Night	Recommended for approval	400,000	Mac Ltd
111555	CA-2	Brian McNight	Approved	400,000	Mac Ltd
222333	CA-1	Tony Low	More info needed	50,000,000	World & Co Ltd
222333	Other	Shelley New	More info provided	50,000,000	World & Co Ltd
222333	CA-3	Brian McNight	Recommended for approval	50,000,000	World & Co Ltd
222333	CM-1	Shane Hollow	Recommended for approval	50,000,000	World & Co Ltd
222333	RCD-1	Nigel New	Approved	50,000,000	World & Co Ltd

In the table in Qlikview I want to see Request ID once, with the relevant task completed by colleague name as follows.

Final Table
Request Id	Task Completed By Colleague Name
111222	Lisa Marie
111333	Tony Low
111444	John Smith
111555	Mary Night
222333	Brian McNight

It is based on the following criteria;

1.       Only show me one task completed by colleague name per request id for those with;

a.       Request status = Recommended for approval or Approved or Declined

b.      Rank Role containing ‘CA’ (I have create a field called rank role prefix to achieve this)

c.       Only one instance per request id ranked by the first instance of part a&b above. (I have created a field called rank role suffix to try to achieve this)

I have used an expression to achieve points a&b,

TT_Request_Status    = {'Approved', 'Declined', 'Recommended for Approval'},
TT_Rank_Role_Prefix  = {'CA' }

But I cannot figure out how to achieve point c.

I tried a calculated dimension, but it’s returning null

=aggr(Rank(TT_Rank_Role_Suffix)=1,TT_Task_Completed_By_Colleague_Name) 

Any help greatly appreciated, and apologies if I am not using the correct terminology

[#2]

here is the QVW file I am using

[#3]

Hi,

Try grouping at the script level like;

Load

.

.

.

Group By [Request Id].

Regards.

[PradeepReddy]

Can you explain/atatch an image for the "Rank"  scenario.?

[#5]

HI this is what i want to see when data is ranked

Final Table
Request Id	Task Completed By Colleague Name
111222	Lisa Marie
111333	Tony Low
111444	John Smith
111555	Mary Night
222333	Brian McNight

but this is what is currently being returned based on the expressions & dimenions i have entered, as you can see there is more than one entry per request id

Request Id	Task Completed By Colleague Name	Request Status
111222	Lisa Marie	Approved
111333	Tony Low	Recommended for approval
111333	Mary Night	Recommended for approval
111333	Brian McNight	Approved
111444	John Smith	Recommended for approval
111555	Mary Night	Recommended for approval
111555	Brian McNight	Approved
222333	Brian McNight	Recommended for approval

[#6]

Hi Priyanka, I have never used Group By before so I am not sure where to put it in my script. here is the current script

LOAD

[Request Id]                                                       as     Key_Request_Id,
[Rank Role]                                                         as       TT_Rank_Role,
left([Rank Role],2)                                              as      TT_Rank_Role_Prefix,
right([Rank Role],1)                                            as      TT_Rank_Role_Suffix,
[Task Completed By Colleague Name]                as     TT_Task_Completed_By_Colleague_Name,
[Request Status]                                                 as      TT_Request_Status,
[Credit Line Amt]                                                  as      TT_Credit_Line_Amt,
Customer                                                             as     TT_Customer

FROM

(

ooxml, embedded labels, table is Sheet1);

thank you for your help