How can I create a Rank field in the Script in the same way as it does in the front end?

[jblomqvist]

Hi,

In the attached app I have used the Rank function to create a ranking for products sold. The highest total sales gets 1 and works its way down.

How can I create the same type of field but in the script?

I realise there is no Rank function in the script so want to know how to do it

[sunny_talwar]

Note: I cannot run the script because I don't have the source files, but try out the below code:

Facts:

LOAD RecNo_OrderDetails,

    Margin,

    OrderID,

    LineNo,

    %Key_Products,

    Quantity,

    UnitPrice,

    Discount,

    LineSalesAmount,

    %Key_Customers,

    %Key_Employees,

    ShipperID,

    FreightWeight,

    OrderDate,

    If(LineSalesAmount <50, 'Less than 50', 'Over 50') as 'Sales Check'

FROM

[..\QVD\Facts.qvd]

(qvd);

Temp:

LOAD %Key_Products,

  Sum(LineSalesAmount) as TotalSales

Resident Facts

Group By %Key_Products;

Join(Facts)

LOAD %Key_Products,

  TotalSales,

  AutoNumber(TotalSales) as Rank

Resident Temp

Order By TotalSales desc;

DROP Table Temp;

Customers:

LOAD RecNo_Customers,

    DivisionID,

    %Key_Customers,

    CompanyName,

    ContactName,

    City,

    Country,

    Address,

    Fax,

    Phone,

    PostalCode,

    StateProvince,

    DivisionName

FROM

[..\QVD\Customers.qvd]

(qvd);

Products:

LOAD RecNo_Products,

    %Key_Categories,

    %Key_Products,

    ProductName,

    SupplierID,

    QuantityPerUnit,

    UnitCost,

    CataloguePrice,

    UnitsInStock,

    UnitsOnOrder,

    CategoryName,

    CategoryDescription

FROM

[..\QVD\Products.qvd]

(qvd);

SalesPersons:

LOAD RecNo_Employees,

    Employees_ID as %Key_Employees,

    %Key_Offices,

    [First Name],

    FullName,

    HireDate,

    [Last Name],

    OfficeAddress,

    OfficeCity,

    OfficeCountry,

    OfficeFax,

    OfficePhone,

    OfficePostalCode,

    OfficeStateProvince,

    [Reports To],

    ResignationDate,

    Title,

    [Year Salary],

    SalesPerson

FROM

[..\External Data\SalesPersons_Offices.xls]

(biff, embedded labels, table is Employees$);

$(Include=..\include\mastercalendar.txt);

[jblomqvist]

Hi Sunny,

This looks good. When I try and apply the same type of code to another app, the result of my AutoNumber does not start at 1. It starts at some other number.

I think this is because I am already using AutoNumber function in another field. If I use AutoNumberHash for example then it works because I am not using AutoNumberHas in another place.

Do you know how I can get it to start at 1?

[sunny_talwar · Accepted solution]

You may have another AutoNumber in your application which might be affecting that behavior. Try this:

AutoNumber(TotalSales, 'Rank') as Rank

[jblomqvist]

Hi Sunny,

That works! Can you please tell me why AutoNumber does this? I wasn't aware that if I used AutoNumber before, a new autonumber field add on top of those values.

Also what is the second parameter in your AutoNumber doing?

[sunny_talwar]

Second parameter is an ID which tells it that this is a different AutoNumber.

Why it does it, I have no idea. May be we can seek hic‌ expertise on this.

Best,

Sunny

[jblomqvist]

Thank you so much for your explanation friend. I have learnt something new today.

[sunny_talwar]

Awesome

I am glad I was able to share my knowledge with you

Best,

Sunny

[hugmarcel]

Hi

see my example file rank.qvw.

- it contains a sub providing flexible ranking mechanisms,
  such as ranking by autonumber or by rowno().
- ranks can be split into half ranks for identical values
  (e.g. rank 11,12,13 -> 12, 12, 12)

https://community.qlik.com/docs/DOC-13949

Regards - Marcel

[piotr-bratek]

Hi Sunny,
what about a situation when we have two Customers with the same TotalSales?
Isn't it, that the autonumber continues with rank 7 instead of 8 ?
That returns an incorrect answer when we ask about e.g. TOP 10 Customers.
Do you know some simple solution to handle this (instead of some next step transformations)?