topic Re: Rank through RowNo() and partition / group by in QlikView

Rank through RowNo() and partition / group by

Thu, 30 Jun 2016 19:34:02 GMT

Hello,

I have a table of sales agents who belong to different teams and I want to rank them by their sales within their respective teams.

How can I rank these agents within their teams through the LOAD script so that their ranks are static? I imagine there must be some PARTITION BY clause that allows me to group the rows by the Team dimension.

I tried creating two separate LOAD scripts and concatenating them but RowNo() seems to be operating on the concatenated table rather than the two tables before the concatenation.

e.g. the following script would give me this:

Rank:

LOAD
Agent
,Team
,Sales
,RowNo() as Rank
Resident AgentSales
WHERE Team = 'Team1'
ORDER BY
Sales DESC
;

CONCATENATE(Rank)

LOAD
Agent
,Team
,Sales
,RowNo() as Rank
Resident AgentSales
WHERE Team = 'Team1'
ORDER BY
Sales DESC
;

*edited submission*

Agent	Team	Sales	Rank
Agent3	Team1	150000	1
Agent1	Team1	100000	2
Agent2	Team1	40000	3
Agent5	Team2	160000	4
Agent6	Team2	120000	5
Agent4	Team2	12000	6

But I actually want this:

Agent	Team	Sales	Rank
Agent3	Team1	150000	1
Agent1	Team1	100000	2
Agent2	Team1	40000	3
Agent5	Team2	160000	1
Agent6	Team2	120000	2
Agent4	Team2	12000	3

Re: Rank through RowNo() and partition / group by

swuehl — Thu, 30 Jun 2016 20:47:05 GMT

Try using Recno() instead of Rowno()

Counters in the Load

Re: Rank through RowNo() and partition / group by

Thu, 30 Jun 2016 21:14:52 GMT

Hi swuehl‌,

The counter seems to be operating post-concatenation rather than pre-concatenation, not sure why that is happening.

The Recno() function did not separate the ranks out by team either. Instead it ranks them regardless of the order added. I incorrectly portrayed the original result set I was getting before so I have edited my submission to reflect what's actually happening with the load script.

By replacing RowNo() with RecNo() I get the following results:

Agent	Team	Sales	Rank
Agent5	Team2	160000	1
Agent3	Team1	150000	2
Agent6	Team2	120000	3
Agent1	Team1	100000	4
Agent2	Team1	40000	5
Agent4	Team2	12000	6

Re: Rank through RowNo() and partition / group by

swuehl — Thu, 30 Jun 2016 21:54:22 GMT

Sorry, I may got confused how your load script really looks like.

Could you please repost your current script, best by adding some small INLINE tables?

Re: Rank through RowNo() and partition / group by

sunny_talwar — Thu, 30 Jun 2016 22:00:32 GMT

How about this:

Rank:
LOAD Agent,

Team,

Sales,

AutoNumber(Sales, Agent) as Rank
Resident AgentSales
ORDER BY Team, Sales DESC;

Change the field Agent to Team in the below post

Re: Rank through RowNo() and partition / group by

sunny_talwar — Thu, 30 Jun 2016 22:03:17 GMT

Sorry my bad:

Rank:
LOAD Agent,

Team,

Sales,

AutoNumber(Sales, Team) as Rank
Resident AgentSales
ORDER BY Team, Sales DESC;

Sample attached

AgentSales:

LOAD * INLINE [

Agent, Team, Sales, Rank

Agent3, Team1, 150000, 1

Agent1, Team1, 100000, 2

Agent2, Team1, 40000, 3

Agent5, Team2, 160000, 4

Agent6, Team2, 120000, 5

Agent4, Team2, 12000, 6

];

Rank:

NoConcatenate

LOAD Agent,

Team,

Sales,

AutoNumber(Sales, Team) as Rank

Resident AgentSales

ORDER BY Team, Sales DESC;

DROP Table AgentSales;

Re: Rank through RowNo() and partition / group by

swuehl — Thu, 30 Jun 2016 22:15:36 GMT

Sal, if you don't want to assign the same rank on a tie break, you can also use

AgentSales:

LOAD * INLINE [

Agent, Team, Sales

Agent3, Team1, 150000

Agent1, Team1, 100000

Agent2, Team1, 40000

Agent5, Team2, 160000

Agent6, Team2, 160000

Agent4, Team2, 12000

];

Rank:

NoConcatenate

LOAD Agent,

Team,

Sales,

AutoNumber(Recno(), Team) as Rank

Resident AgentSales

ORDER BY Sales DESC;

DROP Table AgentSales;

Still I think the Rowno() replaced with Recno() should not give you the output you've posted above. But I am unsure if I understood what you are trying to achieve, i.e. why you are concatenating the same LOAD statement in your adjusted code sample (I guess it's a typo).

Re: Rank through RowNo() and partition / group by

Anonymous — Sun, 22 Apr 2018 17:55:48 GMT

Sunny,

I'm doing this exact thing in an app. The problem is that it is REALLY slow on a large file.

Do you know of a faster method on this topic?

Thanks,

Re: Rank through RowNo() and partition / group by

sunny_talwar — Mon, 23 Apr 2018 01:40:00 GMT

Hey John -

How are you? I have known to see slower performance of AutoNumber to Peek() function... may be you can try with Peek function here

Rank:
LOAD Agent,

Team,

Sales,

If(Team = Previous(Team), RangeSum(Peek('RankPeek'), 1), 1) as RankPeek,

AutoNumber(Sales, Team) as Rank
Resident AgentSales
ORDER BY Team, Sales DESC;

Re: Rank through RowNo() and partition / group by

sunilchakala_ql — Sat, 05 Oct 2019 05:47:22 GMT

Hi Sunny,

if we need to create a rowno based on two dimensions, how can we do ?

like in the above problem, if we need to give rowno based on team and also agent. will autonmbers wroks here?

please help.

Re: Rank through RowNo() and partition / group by

KD2015 — Sun, 05 May 2024 07:11:49 GMT

Hello Sunny & everyone, I am new to Qlik & I have a load script requirement which I have been able to solve with your AutoNumber() & Order By clause recommendations here. However, I have some ties within the ranks & I was wondering if there is a way to skip however many values that are tied when counting the next rank number (e.g. 1,2,3,3,5). I have looked everywhere but I can't find the load script solution. I tried to use Peek() but it produced inaccurate results since the ranked field (Days_w_Excellent_Score) is aggregated & grouped as seen below

Thanks in advance.