Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You may have another AutoNumber in your application which might be affecting that behavior. Try this:
AutoNumber(TotalSales, 'Rank') as Rank
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);
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?
You may have another AutoNumber in your application which might be affecting that behavior. Try this:
AutoNumber(TotalSales, 'Rank') as Rank
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?
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
Thank you so much for your explanation friend. I have learnt something new today.
Awesome
I am glad I was able to share my knowledge with you
Best,
Sunny
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