Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Try using Recno() instead of Rowno()
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 |
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?
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
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;
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).
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
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;
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.