Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Ranking in qlik sense script

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?

8 Replies
devarasu07
Honored Contributor II

Re: Ranking in qlik sense script

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;

agigliotti
Honored Contributor II

Re: Ranking in qlik sense script

Not applicable

Re: Ranking in qlik sense script

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.

devarasu07
Honored Contributor II

Re: Ranking in qlik sense script

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

Not applicable

Re: Ranking in qlik sense script

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;

Not applicable

Re: Ranking in qlik sense script

Forget about num at autonumber

Not applicable

Re: Ranking in qlik sense script

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;

Not applicable

Re: Ranking in qlik sense script

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?

Community Browser