Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fredericvillemi
Creator III
Creator III

Adding rank based on 1 dimension in the script

Hello,

I have a table of sales with two dimensions called Country and Customer and a KPI called Amount.

I would like to add in this table, for each line, the rank of the customer based on the total amount of this Customer in this Country

For example :

Germany, Google, 10

Germany, Google, 5

Germany, Apple, 1

Germany, Microsoft, 100

Belgium, Microsoft, 50

would become :

Germany, Google, 10, rank 2

Germany, Google, 5, rank 2

Germany, Apple, 1, rank 3

Germany, Microsoft, 100, rank 1

Belgium, Microsoft, 50, rank 1

How can I do that in scripting ?

Thanks

1 Solution

Accepted Solutions
sunny_talwar

May be this

Table:

LOAD * INLINE [

    Country, Company, Sales

    Germany, Google, 10

    Germany, Google, 5

    Germany, Apple, 1

    Germany, Microsoft, 100

    Belgium, Microsoft, 50

];

FinalTable:

LOAD *,

If(Country = Previous(Country), If(Company = Previous(Company), Peek('Rank'), RangeSum(Peek('Rank'), 1)), 1) as Rank

Resident Table

Order By Country, Company, Sales desc;

DROP Table Table;

View solution in original post

6 Replies
sunny_talwar

May be this

Table:

LOAD * INLINE [

    Country, Company, Sales

    Germany, Google, 10

    Germany, Google, 5

    Germany, Apple, 1

    Germany, Microsoft, 100

    Belgium, Microsoft, 50

];

FinalTable:

LOAD *,

If(Country = Previous(Country), If(Company = Previous(Company), Peek('Rank'), RangeSum(Peek('Rank'), 1)), 1) as Rank

Resident Table

Order By Country, Company, Sales desc;

DROP Table Table;

fredericvillemi
Creator III
Creator III
Author

Thanks for your example document

It was almost perfect

I just had to change the ORDER BY to :

Order By Country,  Sales desc;

in order to have the sales sorted by total (biggest first) and with the rank depending on the Country

sunny_talwar

But then if you sort by Sales, then the same company can be at different places and you may get different rank for them?

fredericvillemi
Creator III
Creator III
Author

Oh yes you are right .. that's because I have made it on my own project where only one line exists for each Country/Company

But if I take your example Apple is first in Germany where it should be 3rd because of only 1 sale.

What should I do to make your example work ?

sunny_talwar

May be based on total sales

Table:

LOAD * INLINE [

    Country, Company, Sales

    Germany, Google, 10

    Germany, Google, 5

    Germany, Apple, 1

    Germany, Microsoft, 100

    Belgium, Microsoft, 50

];

Left Join (Table)

LOAD Country,

Company,

Sum(Sales) as TotalSales

Resident Table

Group By Country, Company;

FinalTable:

LOAD *,

If(Country = Previous(Country), If(Company = Previous(Company), Peek('Rank'), RangeSum(Peek('Rank'), 1)), 1) as Rank

Resident Table

Order By Country, TotalSales desc, Country;

DROP Table Table;

fredericvillemi
Creator III
Creator III
Author

absolutely excellent !

Thanks