Rank

[krish2459]

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

[Kushal_Chawda · Accepted solution]

@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";

[krish2459]

Hi,

I got the atached output after using te below code.

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;

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.

[sunny_talwar]

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