Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
sifatnabil
Specialist
Specialist

Create a table of ranks in load script

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.

12 Replies
Anonymous
Not applicable

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
Specialist
Specialist
Author

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

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;