Create a table of ranks in load script

[sifatnabil]

Hi,

I would like to create a table of rankings in the load script based on revenue by client by week.

i.e. the table created would be

WeekStarting, Client, Rank, Revenue,

1/1/2015, A, 1, 100,000,

1/1/2015, B, 2, 90,000,

1/1/2015, C, 3, 80,000,

Thanks.

[robert_mika]

It looks like you have all your data already.

What's the requirement?

Could you elaborate?

[awhitfield]

HI Sifat,

I presume you are trying to Rank by revenue? Rank is a Chart function, adding an extra rank expression to your chart should do the trick, e.g.

Rank(Sum(Revenue))

Andy

[sifatnabil]

Hi Robert,

I do not have the field "Rank". How do I produce this table such that it gives a correct rank per week? So for Week 1/1/2015, if there are 100 clients, the "rank" column will have 1 to 100 rank numbers for that week. Then for next week, another 100 rank numbers from 0 to 100. Do note that I need to create this table as a Resident table.

Hi Andy,

I am trying to put this in the script as the Rank function is causing issues due to selections on charts. I feel a hard coded rank number would be much better.

[awhitfield]

There is an old post on this subject that may help!

http://community.qlik.com/thread/3540

Andy

[sunny_talwar]

See if this helps:

Table:

LOAD * INLINE [

    WeekStarting, Client, Revenue

    1/1/2015, A, 100000

    1/1/2015, B, 90000

    1/1/2015, C, 80000

    1/8/2015, A, 40000

    1/8/2015, B, 55000

    1/8/2015, C, 67000

    1/15/2015, A, 39000

    1/15/2015, B, 98000

    1/15/2015, C, 45000

];

Table1:

LOAD *,

  AutoNumber(WeekStarting) as Count

Resident Table;

Count:

LOAD Count(DISTINCT WeekStarting) as WeekCount

Resident Table1;

LET vWeekCount = Peek('WeekCount');

FOR i = 1 to vWeekCount

  Join(Table1)

  LOAD WeekStarting,

  Revenue,

  Client,

  Count,

  RowNo() as Rank

  Resident Table1

  Where Count = $(i)

  Order By Revenue desc;

NEXT

DROP Tables Table, Count;

Table2:

LOAD WeekStarting,

  Client,

  Revenue,

  Rank

Resident Table1

Where Len(Trim(Rank)) <> 0;

DROP Table Table1;

Best,

S

[sifatnabil]

Thanks sunindia ! Is it possible to amend this to rank only the top 10 clients by revenue instead of all clients? This will significantly reduce the size of the rank table.

[anbu1984]

Table:

LOAD * INLINE [

    WeekStarting, Client, Revenue

    1/1/2015, A, 100000

    1/1/2015, B, 90000

    1/1/2015, C, 80000

    1/8/2015, A, 40000

    1/8/2015, B, 55000

    1/8/2015, C, 67000

    1/15/2015, A, 39000

    1/15/2015, B, 98000

    1/15/2015, C, 45000

];

Final:

Load * Where Rank <= 10;

Load *,Autonumber(Recno(),WeekStarting) As Rank;

Load * Resident Table Order by WeekStarting desc, Revenue desc;

Drop Table Table;

[sifatnabil]

Many thanks anbu1984 Sorry I forgot to specify another requirement: the "Revenue" should be a sum of Revenue, i.e. sum(Revenue), this would mean GROUP BY statement is required. Can this be amended in your script?

[anbu1984]

Why do want to sum revenue?

Do you have two rows with same client and Weekstarting like below?

LOAD * INLINE [

    WeekStarting, Client, Revenue

    1/1/2015, A, 100000

    1/1/2015, A, 500000 ];