8 Replies Latest reply: Jun 19, 2017 2:11 AM by kumarsuresh lulu RSS

    Ranking in qlik sense script

    kumarsuresh lulu

      Can any body explain how to implement ranking in qlik sense script part.

      Rank function is presentation function so, is there any other way to implement it?

        • Re: Ranking in qlik sense script
          Devarasu R

          Staff:

          LOAD * INLINE [

              Staff, Salary

              Davolio, 5037

              Fuller, 4028

              Leverling, 16112

              Peacock, 3693

              Buchanan, 4103

              Suyama, 11489

              King, 4383

              Callahan, 3510

              Dodsworth, 4383

              Leverling, 3065

              King, 11489

              Deva,30000

          ];

           

          NoConcatenate

          Saff_Temp:

          Load Staff, SUM(Salary) as Salary

             Resident Staff

          Group By Staff;

           

          NoConcatenate

          Final:

          First 5

          Load Staff, Salary Resident Saff_Temp Order By Salary Desc;

           

          Drop Tables Staff, Saff_Temp;

          • Re: Ranking in qlik sense script
            kumarsuresh lulu

            Thanks Devarasu for your answer,

            but in single table I have to rank for more than three columns, that to not five or ten , we have to find the rank up to last value.

              • Re: Ranking in qlik sense script
                Devarasu R

                Hi,

                 

                Try like below,

                Table:

                LOAD * INLINE [

                    Company,Country,Staff, Salary

                    ABC,India,Davolio, 5037

                    ABC,Singapore,Fuller, 4028

                    XYZ,India,Leverling, 16112

                    XYZ,India,Peacock, 3693

                    XYZ,Singapore,Buchanan, 4103

                    XYZ,Singapore,Suyama, 11489

                    ABC,India,King, 4383

                    ABC,India,Callahan, 3510

                    XYZ,Singapore,Dodsworth, 4383

                    ABC,India,Leverling, 3065

                    ABC,Singapore,King, 11489

                ];

                 

                FinalTable:

                LOAD *,

                  AutoNumber(Salary, Company&Country) as RANK

                Resident Table

                Order By  Company,Country, Salary desc;

                 

                DROP Table Table;

                rank2.JPG

                 

                 

                Hope this helps you

                 

                Regards,

                Deva

              • Re: Ranking in qlik sense script
                kumarsuresh lulu

                I have these many below columns bu I have find rank like below code. Is it fine?

                based on your code I have created but for order I have taken only key columns.

                Load

                FleetAutoKey,

                Fleet_AssetID,

                RollingStockKey,

                EquipmentType,

                EquipmentCategory,

                CalendarKey,

                AssetMiles,

                Tripcount,

                AssetDelayMinutes,

                PrimaryFailuresCount,

                MMBF,

                AverageAssetDelayMinutes,

                Num(AutoNumber(MMBF40%+AverageAssetDelayMinutes40%+PrimaryFailuresCount20%

                ,Fleet_AssetID&RollingStockKey&CalendarKey&EquipmentType&EquipmentCategory),'##.0')      as Ranking

                Resident FleetPerf_Pfl

                where date(CalendarKey,'YYYYMMDD')=Date(date((Today()-1),'YYYYMMDD')-90,'YYYYMMDD')

                order by FleetAutoKey,

                Fleet_AssetID,

                RollingStockKey,

                EquipmentType,

                EquipmentCategory,

                CalendarKey,MMBF40%,AverageAssetDelayMinutes40%,PrimaryFailuresCount20% desc;

                • Re: Ranking in qlik sense script
                  kumarsuresh lulu

                  Forget about num at autonumber

                  • Re: Ranking in qlik sense script
                    kumarsuresh lulu

                    please find this code

                     

                     

                    Load

                    FleetAutoKey,

                    Fleet_AssetID,

                    RollingStockKey,

                    EquipmentType,

                    EquipmentCategory,

                    CalendarKey,

                    AssetMiles,

                    Tripcount,

                    NumOfFailures,

                    AssetDelayMinutes,

                    PrimaryFailuresCount,

                    MMBF,

                    MMBF40%,

                    AverageAssetDelayMinutes40%,

                    AverageAssetDelayMinutes,

                    PrimaryFailuresCount20%,

                    AutoNumber((MMBF40%)/90

                    ,FleetAutoKey&Fleet_AssetID&RollingStockKey&CalendarKey&EquipmentType&EquipmentCategory)     as MMBFRanking,

                    AutoNumber((AverageAssetDelayMinutes40%)/90

                    ,FleetAutoKey&Fleet_AssetID&RollingStockKey&CalendarKey&EquipmentType&EquipmentCategory)     as AverageAssetDelayMinutesRanking,

                    AutoNumber((PrimaryFailuresCount20%)/90

                    ,FleetAutoKey&Fleet_AssetID&RollingStockKey&CalendarKey&EquipmentType&EquipmentCategory)     as PrimaryFailuresCountRanking

                    Resident FleetPerf_Pfl_TempFl

                    //where date(CalendarKey,'YYYYMMDD')=Date(date((Today()-1),'YYYYMMDD')-90,'YYYYMMDD')

                    order by FleetAutoKey,

                    Fleet_AssetID,

                    RollingStockKey,

                    EquipmentType,

                    EquipmentCategory,

                    CalendarKey,MMBF40%,AverageAssetDelayMinutes40%,PrimaryFailuresCount20% desc;

                    • Re: Ranking in qlik sense script
                      kumarsuresh lulu

                      Rank of  [ 40% of (Sum of MMBF Rank of previous 90 days) + 40% of (Sum of  PrimaryFailuresCount  Rank of previous 90 days) + 20% of (Sum of Average AssetDelayMinutes Rank of previous 90 days)] / 90



                      This is my original requirement, can any one suggest me how to do this in script?