topic Re: Calculated Dimension Or Expression needed to Rank data in QlikView

Calculated Dimension Or Expression needed to Rank data

Wed, 25 Jun 2014 10:01:12 GMT

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

Re: Calculated Dimension Or Expression needed to Rank data

Wed, 25 Jun 2014 10:47:52 GMT

here is the QVW file I am using

Re: Calculated Dimension Or Expression needed to Rank data

Wed, 25 Jun 2014 11:28:21 GMT

Hi,

Try grouping at the script level like;

Load

Group By [Request Id].

Regards.

Re: Calculated Dimension Or Expression needed to Rank data

PradeepReddy — Wed, 25 Jun 2014 11:53:23 GMT

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

Re: Calculated Dimension Or Expression needed to Rank data

Wed, 25 Jun 2014 13:38:53 GMT

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

Re: Calculated Dimension Or Expression needed to Rank data

Wed, 25 Jun 2014 13:56:36 GMT

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