topic Re: Rank in QlikView

Rank

krish2459 — Fri, 15 Nov 2024 23:51:52 GMT

Hi ,

I need to derive the rank in the below format.

Highlighted column is the desired out put.

Row Labels	Sum of Weighted	Normal Rank	Required Rank
Falkland Islands	1	1	1
Montserrat	1	1	1
Anguilla	2.77815125	2	3
Cayman Islands	2.77815125	2	3
Dominica	2.77815125	2	3
Saint Kitts and Nevis	2.77815125	2	3
Saint-Martin	2.77815125	2	3
Grenada	3.77815125	3	8
Qatar	3.77815125	3	8
Saint Vincent and Grenadines	4.455773409	4	10
Caribbean Netherlands	4.556302501	5	11
United Arab Emirates	4.77815125	6	12
Aruba	6.430300856	7	13
Virgin Islands	7.023499423	8	14
Bahamas	7.23392466	9	15
Turks and Caicos Islands	9.478098186	10	16

Re: Rank

Kushal_Chawda — Mon, 05 Oct 2020 13:22:29 GMT

@krish2459 try below expression. Replace highlighted by your actual sum of weight expression. You can remove the "-" sign if you want to rank highest weight as 1 and so on

=rank(total -sum([Sum of Weighted]),0,1)

Re: Rank

sunny_talwar — Mon, 05 Oct 2020 12:53:40 GMT

@krish2459 This post I wrote sometime ago might come handy here

Continuous Ranking (no missing Rank)

Re: Rank

Kushal_Chawda — Mon, 05 Oct 2020 13:16:11 GMT

@krish2459 Updated my previous reply as don't need rounding

Re: Rank

krish2459 — Mon, 05 Oct 2020 13:32:43 GMT

Is it possible to achieve in load script.

Re: Rank

Kushal_Chawda — Mon, 05 Oct 2020 13:49:00 GMT

@krish2459 Can you share sample data? Also Sum Of weight is actual field or calculated?

Re: Rank

sunny_talwar — Mon, 05 Oct 2020 13:52:02 GMT

@krish2459 try this

Table: LOAD * INLINE [ Row Labels, Sum of Weighted Falkland Islands, 1 Montserrat, 1 Anguilla, 2.77815125 Cayman Islands, 2.77815125 Dominica, 2.77815125 Saint Kitts and Nevis, 2.77815125 Saint-Martin, 2.77815125 Grenada, 3.77815125 Qatar, 3.77815125 Saint Vincent and Grenadines, 4.455773409 Caribbean Netherlands, 4.556302501 United Arab Emirates, 4.77815125 Aruba, 6.430300856 Virgin Islands, 7.023499423 Bahamas, 7.23392466 Turks and Caicos Islands, 9.478098186 ]; FinalTable: LOAD *, RangeSum(Peek('tmpRank'), 1) as tmpRank, If([Sum of Weighted] = Previous([Sum of Weighted]), Peek('Required Rank'), RangeSum(Peek('tmpRank'), 1)) as [Required Rank] Resident Table Order By [Sum of Weighted]; DROP Table Table; DROP Field tmpRank;

Re: Rank

krish2459 — Mon, 05 Oct 2020 14:11:06 GMT

Hi,

Here is the script.

join

Load sub_event_type,Country,
if(sum(fatalities)>0,2,1) as EventWeight,
if(sum(fatalities)>0,2,1)*sum(interaction) as "Event Weightage"

Resident Armed_Conflict_Temp Group by sub_event_type,Country;

Attached is the sample data.

Thanks

Re: Rank

Kushal_Chawda — Mon, 05 Oct 2020 14:21:02 GMT

@krish2459 What is expected output based on this data?

Re: Rank

krish2459 — Mon, 05 Oct 2020 14:40:36 GMT

to get similar logic from excel

"=RANK.EQ(J4,$J$4:$J$158,1)/COUNT($J$4:$J$158)"

Percentile of "Event Weightage" based on "Event Weightage" rank.

Re: Rank

Kushal_Chawda — Mon, 05 Oct 2020 14:51:11 GMT

@krish2459 try below

Armed_Conflict_Temp: LOAD sub_event_type, event_type, interaction, region, trim(upper(country)) as Country, trim(upper(country)) as Country4, admin1, fatalities FROM [lib://ISC_Data (code1_320001959)/Projects/Secuity Analytics/Risk Management/Development/QVDs/Armed Conflict/Armed Conflict.qvd] (qvd)where Len(trim(location))>0; join Load sub_event_type,Country, if(sum(fatalities)>0,2,1) as EventWeight, if(sum(fatalities)>0,2,1)*sum(interaction) as "Event Weightage" Resident Armed_Conflict_Temp Group by sub_event_type,Country; Left join LOAD Distinct Country, "Event Weightage", If("Event Weightage" = Previous("Event Weightage" ), Peek('Required Rank'), RangeSum(Peek('Required Rank'), 1)) as [Required Rank] Resident Armed_Conflict_Temp Order by "Event Weightage";

Re: Rank

krish2459 — Mon, 05 Oct 2020 14:52:53 GMT

Hi,

I got the atached output after using te below code.

join

Load sub_event_type,Country,
if(sum(fatalities)>0,2,1) as EventWeight,
if(sum(fatalities)>0,2,1)*sum(interaction) as "Event Weightage"

Resident Armed_Conflict_Temp Group by sub_event_type,Country;

Armed_Conflict:
LOAD *,
RangeSum(Peek('tmpRank'), 1) as tmpRank,
If("Event Weightage" = Previous("Event Weightage"), Peek('Required Rank'), RangeSum(Peek('tmpRank'), 1)) as [Required Rank]
Resident Armed_Conflict_Temp

Order By "Event Weightage";

DROP Table Armed_Conflict_Temp;
DROP Field tmpRank;

It seems countrys has multiple ranks.

Please check once.

Re: Rank

sunny_talwar — Mon, 05 Oct 2020 15:09:06 GMT

@krish2459 What you have attached is what you want or is this something you get and is not what you want?