Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jblomqvist
Contributor II

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

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

1 Solution

Accepted Solutions

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

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

AutoNumber(TotalSales, 'Rank') as Rank

8 Replies

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

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
Contributor II

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

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?

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

AutoNumber(TotalSales, 'Rank') as Rank

jblomqvist
Contributor II

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

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?

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

Capture.PNG

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

Best,

Sunny

jblomqvist
Contributor II

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

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?

Awesome

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

Best,

Sunny

hugmarcel
Contributor III

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

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

Community Browser