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

    Rank IP list

    Michele De Nardi

      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
          Michele De Nardi

          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);