community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How To Sort IP Addresses

Meteor

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.

Alteryx Certified Partner
Alteryx Certified Partner

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

Alteryx Certified Partner
Alteryx Certified Partner

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

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.

Meteor

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

So, this is pretty much it.

Labels