Ranking in qlik sense script

[#1]

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?

[devarasu07]

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]

take a look here Re: Rank Function load script

[#4]

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]

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;

Hope this helps you

Regards,

Deva

[#6]

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;

[#7]

Forget about num at autonumber

[#8]

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;

[#9]

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?