Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
fredericvillemi
Contributor 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

Tags (3)
1 Solution

Accepted Solutions

Re: Adding rank based on 1 dimension in the script

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;

6 Replies

Re: Adding rank based on 1 dimension in the script

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

Re: Adding rank based on 1 dimension in the script

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

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

Re: Adding rank based on 1 dimension in the script

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

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

Re: Adding rank based on 1 dimension in the script

absolutely excellent !

Thanks