topic Re: Ranking in Backend in App Development

Ranking in Backend

ushakumari1_ — Mon, 01 Jul 2024 15:55:48 GMT

I need to rank this data in the table below in the backend. Can someone help?

Region	Sales
North	5000
South	1500
East	1200
West	1800

Desired output

Region	Sales	Rank
North	5000	1
West	1800	2
South	1500	3
East	1200	4

Re: Ranking in Backend

chriscammers — Mon, 01 Jul 2024 20:33:48 GMT

The important thing to remember here is that you have to do two operations which you can do simultaneously but one of them has a restriction. The first operation to to sort the records the second is to figure out the rank.

When you load data you can use an "order by" clause but this clause only works on data that is loaded via a resident load.

So no matter what you do this is going to have to be a two step process.

Load the data in RAM
Sort and rank the data

Temp: Load * Inline [ Region,Sales North,5000 South,1500 East,1200 West,1800 ] Ranked: Load Region, Sales, RowNo() as Rank Resident Temp Order by Sales Desc; Drop Table Temp;

the RowNo function provides an autonumber based on the sequence of the records in the resulting table.

Re: Ranking in Backend

Vegar — Tue, 02 Jul 2024 05:04:00 GMT

The solution from @chriscammers should solve your issue as you described it.

You could also have a look at the quite new WRank.

Re: Ranking in Backend

chriscammers — Tue, 02 Jul 2024 13:47:39 GMT

@Vegar Any idea when wRank was? Depending on how recent any idea if it has made it to Qlik Sense on Windows??

I definitely would have suggested WRank had I known about it.

If you don't have WRank on your version then to serve the partition function you can use Autonumber(Value,PartitionDimension) instead of Rowno().