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

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

jblomqvist — Mon, 26 Oct 2015 14:12:07 GMT

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

Re: [App attached] How can I create a Rank field in the Script in the same way as it does in the front end?

sunny_talwar — Mon, 26 Oct 2015 14:16:50 GMT

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);

Re: [App attached] How can I create a Rank field in the Script in the same way as it does in the front end?

jblomqvist — Mon, 26 Oct 2015 14:46:55 GMT

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?

Re: [App attached] How can I create a Rank field in the Script in the same way as it does in the front end?

sunny_talwar — Mon, 26 Oct 2015 14:50:01 GMT

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

AutoNumber(TotalSales, 'Rank') as Rank

Re: [App attached] How can I create a Rank field in the Script in the same way as it does in the front end?

jblomqvist — Mon, 26 Oct 2015 14:58:22 GMT

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?

Re: [App attached] How can I create a Rank field in the Script in the same way as it does in the front end?

sunny_talwar — Mon, 26 Oct 2015 15:03:21 GMT

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

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

jblomqvist — Mon, 26 Oct 2015 15:24:09 GMT

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

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

sunny_talwar — Mon, 26 Oct 2015 15:25:44 GMT

Awesome

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

Best,

Sunny

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

hugmarcel — Sun, 15 Nov 2015 11:20:53 GMT

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

Re: [App attached] How can I create a Rank field in the Script in the same way as it does in the front end?

piotr-bratek — Tue, 11 Feb 2025 09:32:42 GMT

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)?