Rank IP list

micheledenardi — Wed, 14 Jun 2017 09:12:30 GMT

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

Re: Rank IP list

micheledenardi — Wed, 14 Jun 2017 09:46:17 GMT

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,

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

topic Re: Rank IP list in App Development

Rank IP list

Re: Rank IP list