Rank IP list

[micheledenardi]

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 ?

Region	IP	Actual Rank (wrong)	Rank Expected
1	2.68.55.1	4	1
1	21.1.6.2	1	3
1	102.1.1.1	3	4
1	9.2.82.6	2	2
2	233.55.18.97	3	4
2	10.6.1.5	1	1
2	9.89.107.245	4	3
2	10.6.1.49	2	2

[micheledenardi · Accepted solution]

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

Load

  Region,

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

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

Load

  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

;

LOAD Region,

     IP

FROM IPs.xlsx (ooxml, embedded labels, table is Foglio1);