How To Sort IP Addresses

[jrdepriest]

The default sorter for IP addresses in Alteryx is what you'd expect. It doesn't understand how octets work and sorts weirdly.

With this input list

IP Addresses

10.10.10.100

10.10.10.11

10.10.10.1

10.10.100.100

10.10.11.11

10.10.1.1

Alteryx will happily spit out this when you try to sort Ascending by IP Address.

IP Address

10.10.1.1

10.10.10.1

10.10.10.100

10.10.10.11

10.10.100.100

10.10.11.11

As you can see, it puts '100' before '11' in all cases because it is sorting each individual digit instead of each octet.

A super-simple way to fix this is to split the IP into individual octets and sort on those.

I broke it into octets using a regular expression.

(\d+)\.(\d+)\.(\d+)\.(\d+)

Using the Parse Output Method to create new columns from each octet.

Then you just sort ascending on 1st, 2nd, 3rd, then 4th and your list is sorted properly.

IP Address

10.10.1.1

10.10.10.1

10.10.10.11

10.10.10.100

10.10.11.11

10.10.100.100

Just leave the four extra columns off of your Select since they are extraneous.

I really wish Alteryx had an IP sort built-in, but it's easy enough to add this to any workflow working with IP addresses. If it were built-in, there could also be logic built in to identify valid and invalid IP addresses such that 265.354.25.1 would be rejected.

[BenMoss]

Never dealt with IP addresses in my role but this is awesome. 

Perhaps you could think about integrating this into a macro so others don't have to struggle where you have before.

If you don't feel like you have the capacity to do this then i'd be willing to help you with this.

Ben

[MarqueeCrew · Accepted solution]

@jrdepriest & @BenMoss,

I've posted a macro to the gallery to satisfy this request.  It will provide feedback on:

• Is the IP valid?
• Is the IP in a public or private range?

It has an optional sort feature for all valid IP addresses.  The macro is attached here or can be found at: https://gallery.alteryx.com/#!app/IP-Address-Validation/58f796baf499c713d420a673

Cheers,

Mark

[jrdepriest]

I'm pretty new to Alteryx and I do not know how to create a Macro.

I'd be open to it, because I have other things that I'd like to use in multiple places without recreating the wheel every time.

Almost all of the data I have to work with has an IP address in it somewhere. Often, it is what we key off of to join records.

[jrdepriest]

This macro has the sorting part built-in among other things.

So, this is pretty much it.