Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank through RowNo() and partition / group by

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*   

AgentTeamSalesRank
Agent3Team11500001
Agent1Team11000002
Agent2Team1400003
Agent5Team21600004
Agent6Team21200005
Agent4Team2120006

But I actually want this:

   

AgentTeamSalesRank
Agent3Team11500001
Agent1Team11000002
Agent2Team1400003
Agent5Team21600001
Agent6Team21200002
Agent4Team2120003
9 Replies
swuehl
MVP
MVP

Try using Recno() instead of Rowno()

Counters in the Load

Not applicable

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:

AgentTeamSalesRank
Agent5Team21600001
Agent3Team11500002
Agent6Team21200003
Agent1Team11000004
Agent2Team1400005
Agent4Team2120006
swuehl
MVP
MVP

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

Capture.PNG

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
MVP
MVP

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

jcooney1
Contributor III
Contributor III

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
Contributor III
Contributor III

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.