topic Re: Create a table of ranks in load script in QlikView

Create a table of ranks in load script

sifatnabil — Mon, 23 Feb 2015 08:00:43 GMT

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.

Re: Create a table of ranks in load script

robert_mika — Mon, 23 Feb 2015 10:32:07 GMT

It looks like you have all your data already.

What's the requirement?

Could you elaborate?

Re: Create a table of ranks in load script

awhitfield — Mon, 23 Feb 2015 10:43:29 GMT

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

Re: Create a table of ranks in load script

sifatnabil — Mon, 23 Feb 2015 10:48:31 GMT

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.

Re: Create a table of ranks in load script

awhitfield — Mon, 23 Feb 2015 10:58:19 GMT

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

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

Andy

Re: Create a table of ranks in load script

sunny_talwar — Mon, 23 Feb 2015 11:09:26 GMT

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,

Re: Create a table of ranks in load script

sifatnabil — Wed, 25 Feb 2015 08:06:05 GMT

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.

Re: Create a table of ranks in load script

anbu1984 — Wed, 25 Feb 2015 08:31:36 GMT

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;

Re: Create a table of ranks in load script

sifatnabil — Wed, 25 Feb 2015 08:38:31 GMT

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?

Re: Create a table of ranks in load script

anbu1984 — Wed, 25 Feb 2015 08:54:18 GMT

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 ];

Re: Create a table of ranks in load script

Anonymous — Wed, 25 Feb 2015 09:07:25 GMT

Hi Sifat,

Try:

Temp:
LOAD customer,
sum(Revenue) as sum_Revenue
FROM Table1
GROUP BY customer;

Table:
LOAD Customer,
sum_Revenue,
if( Peek('sum_Revenue') = sum_Revenue,Peek('RankQualified'),RecNo()) as RankQualified
RESIDENT Temp
ORDER BY sum_Revenue desc ; // or asc

DROP TABLE Temp;

Regards

Neetha

Re: Create a table of ranks in load script

sifatnabil — Wed, 25 Feb 2015 09:08:10 GMT

anbu1984 Yes that is correct. the resident table the data is sourced from has a revenue figure for each trade done. Thus, it will look like the inline table in your reply.

Re: Create a table of ranks in load script

anbu1984 — Wed, 25 Feb 2015 09:21:30 GMT

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

];

Join(Table)

Load WeekStarting, Client, Sum(Revenue) As Revenue_Sum Resident Table;

Final:

Load * Where Rank <= 10;

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

Load Distinct WeekStarting, Client,Revenue_Sum Resident Table Order by WeekStarting desc, Revenue_Sum desc;

Drop Table Table;