1 Reply Latest reply: Jun 14, 2017 5:52 AM by Michele De Nardi

# Rank IP list

Hi,

i've got a list of IP which i want to rank by region/ip.

If i simply remove all dots, the rank is wrong because indipendently from the final number lenght, the numbers on the left are more important than numbers on the right.

How to achieve this ?

RegionIPActual Rank (wrong)Rank Expected
12.68.55.141
121.1.6.213
1102.1.1.134
19.2.82.622
2233.55.18.9734
210.6.1.511
29.89.107.24543
210.6.1.4922
• ###### Re: Rank IP list

Doing this I've Normalized my ip format and now i can use rank()/min()/max() function on [Normalized IP] field.

Region,

dual(IP,FirstPart&SecondPart&ThirdPart&FourthPart) as IP,

FirstPart&SecondPart&ThirdPart&FourthPart as [Normalized IP];

Region,

IP,

right('111'&left(IP,index(IP,'.')-1),3) as FirstPart,

right('111'&mid(IP,index(IP,'.')+1,index(IP,'.',2)-index(IP,'.')-1),3) as SecondPart,

right('111'&mid(IP,index(IP,'.',2)+1,index(IP,'.',3)-index(IP,'.',2)-1),3) as ThirdPart,

right('111'&mid(IP,index(IP,'.',3)+1,len(IP)-index(IP,'.',3)),3) as FourthPart

;