Rank through RowNo() and partition / group by

[#1]

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

[swuehl]

Try using Recno() instead of Rowno()

Counters in the Load

[#3]

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

[swuehl]

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?

[sunny_talwar]

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

[sunny_talwar]

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;

[swuehl]

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

[Anonymous]

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,

JC

[sunny_talwar]

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;

[sunilchakala_ql]

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.