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.

[Anonymous]

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

[sifatnabil]

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.

[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

];

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;