topic Re: Adding rank based on 1 dimension in the script in QlikView

Adding rank based on 1 dimension in the script

fredericvillemi — Tue, 24 Oct 2017 11:41:09 GMT

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

Re: Adding rank based on 1 dimension in the script

sunny_talwar — Tue, 24 Oct 2017 11:47:30 GMT

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;

Re: Adding rank based on 1 dimension in the script

fredericvillemi — Tue, 24 Oct 2017 13:52:46 GMT

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

Re: Adding rank based on 1 dimension in the script

sunny_talwar — Tue, 24 Oct 2017 14:10:25 GMT

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

Re: Adding rank based on 1 dimension in the script

fredericvillemi — Tue, 24 Oct 2017 14:32:33 GMT

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 ?

Re: Adding rank based on 1 dimension in the script

sunny_talwar — Tue, 24 Oct 2017 14:37:23 GMT

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;

Re: Adding rank based on 1 dimension in the script

fredericvillemi — Tue, 24 Oct 2017 15:12:32 GMT

absolutely excellent !

Thanks