5 Replies Latest reply: Jun 25, 2014 9:56 AM by Siobhan McNulty RSS

    Calculated Dimension Or Expression needed to Rank data

      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
            Priyanka Rao

            Hi,

            Try grouping at the script level like;

            Load

            .

            .

            .

             

            Group By [Request Id].

             

            Regards.

              • Re: Calculated Dimension Or Expression needed to Rank data

                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


                [C:\Siobhan\Everday Files\Qlikview files\source files\Test Data June 24th.xlsx]
                (

                ooxml, embedded labels, table is Sheet1);

                 

                thank you for your help

              • Re: Calculated Dimension Or Expression needed to Rank data
                pradeep t

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

                  • Re: Calculated Dimension Or Expression needed to Rank data

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

                    Final Table
                    Request IdTask Completed By Colleague Name
                    111222Lisa Marie
                    111333Tony Low
                    111444John Smith
                    111555Mary Night
                    222333Brian 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 IdTask Completed By Colleague NameRequest Status
                    111222Lisa MarieApproved
                    111333Tony LowRecommended for approval
                    111333Mary NightRecommended for approval
                    111333Brian McNightApproved
                    111444John SmithRecommended for approval
                    111555Mary NightRecommended for approval
                    111555Brian McNightApproved
                    222333Brian McNightRecommended for approval