Adding rank based on 1 dimension in the script

[fredericvillemi]

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

[sunny_talwar · Accepted solution]

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]

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]

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]

absolutely excellent !

Thanks