topic Re: Ranking in Script in QlikView

Ranking in Script

Anonymous — Tue, 03 Apr 2018 05:43:39 GMT

Hi,

I need help with this Rank situation (Aggr with Dimensions) and it must be made on Script (Not in set analysis).

Situation:

Platform	Site	Value
Mobile	EN	100
Mobile	IL	300
Mobile	DE	150
PC	EN	200
PC	IL	400
PC	DE	600
Crypto	EN	400
Crypto	IL	300
Crypto	DE	100

Wanted Result:

Platform	Site	Value	Rank
Mobile	EN	100	3
Mobile	IL	300	1
Mobile	DE	150	2
PC	EN	200	3
PC	IL	400	2
PC	DE	600	1
Crypto	EN	400	1
Crypto	IL	300	2
Crypto	DE	100	3

Re: Ranking in Script

tresB — Tue, 03 Apr 2018 05:55:00 GMT

Try like:

T1:

Load * Inline [

Platform, Site, Value

Mobile, EN, 100

Mobile, IL, 300

Mobile, DE, 150

PC, EN, 200

PC, IL, 400

PC, DE, 600

Crypto, EN, 400

Crypto, IL, 300

Crypto, DE, 100];

Join

Load

Platform, Site,

AutoNumber( Rowno(), Platform) as Rank

Resident T1 Order By Value;

Re: Ranking in Script

tamilarasu — Tue, 03 Apr 2018 05:57:52 GMT

Hi Meitar,

Data:

LOAD * INLINE [

Platform, Site, Value

Mobile, EN, 100

Mobile, IL, 300

Mobile, DE, 150

PC, EN, 200

PC, IL, 400

PC, DE, 600

Crypto, EN, 400

Crypto, IL, 300

Crypto, DE, 100

];

Result:

Load *,

AutoNumber(Value, Platform) as Rank

Resident Data

Order By Platform, Value desc;

DROP Table Data;

Re: Ranking in Script

chinnuchinni — Tue, 03 Apr 2018 06:13:26 GMT

try this:

Situation:

load * Inline [

Platform, Site, Value

Mobile, EN, 100

Mobile, IL, 300

Mobile, DE, 150

PC, EN, 200

PC, IL, 400

PC, DE, 600

Crypto, EN, 400

Crypto, IL, 300

Crypto, DE, 100

];

abc:

load Site, sum(Value)as Valuee1 Resident Situation Group by Site;

load Site,Valuee1,RowNo() as rank Resident abc Order by Valuee1 desc;

DROP Table abc;

Re: Ranking in Script

Anonymous — Tue, 03 Apr 2018 11:11:38 GMT

It's correct for 1 Measure (Value).

When I'm trying to this on more than one measure I don't succeed- Only for the first Measure

(As I read it because of AutoNumber function).

Any solution for that?

Re: Ranking in Script

sunny_talwar — Tue, 03 Apr 2018 12:00:36 GMT

Would you be able to elaborate on your concern?

Re: Ranking in Script

Anonymous — Tue, 03 Apr 2018 12:16:43 GMT

Re: Ranking in Script

Anonymous — Tue, 03 Apr 2018 12:17:33 GMT

As I Read maybe it's because of Autonumber (Need to reset it somehow)

Re: Ranking in Script

sunny_talwar — Tue, 03 Apr 2018 12:21:04 GMT

Change them like this and see if it works

AutoNumber(Weeklypageviews, Product&domain_ID&1) AS Weeklypageviews_Rank

AutoNumber(Weeklyvisits, Product&domain_ID&2) AS Weeklyvisits_Rank

AutoNumber(Weeklyvisitors, Product&domain_ID&3) AS Weeklyvisitors_Rank

Re: Ranking in Script

sasiparupudi1 — Tue, 03 Apr 2018 12:44:39 GMT

Complete your joins first into a temp table

and try autounumber on the final table

hth

Sasi

Re: Ranking in Script

Anonymous — Tue, 03 Apr 2018 12:45:06 GMT

Geat Thanks!!!

Re: Ranking in Script

padales1 — Fri, 06 Mar 2020 12:50:07 GMT

Hi Sunny,

Could you please explain how adding 1 and 2 make this work? what is this identifier?

Re: Ranking in Script

fabdulazeez — Tue, 15 Sep 2020 12:37:27 GMT

I think the autonumber returns a unique integer value for each distinct evaluated value for entire script execution.So for the second and third time,it can encounter a similar value as the previous one. To make it different counter instances we can add something(here 1 and 2 ) to make it distinct