Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Use RegEx and build a Macro to Validate and sort IP Address

MarqueeCrew
20 - Arcturus
20 - Arcturus
Created

@jrdepriestrecently wrote to the Alteryx Community askingHow-To-Sort-IP-Addresses? In response to his request, I provided him a macro that not only sorts the data, but also validates that the values are in range {0-255}.{0-255}.{0-255}.{0-255}. The solution post includes the macro as well as having posted the macro to the Alteryx Gallery for all members to access:IP Address Validation

Now I'm now following through with a Knowledge Base article and will review my macro with anyone interested in the following topics:

  • RegEx (Regular Expression) validation of an IP Address
  • Building a Standard Macro
    • Mapping to an incoming field
    • Reverse Field Maps (aka keeping the incoming field name for your IP Address)
    • Conditional processing via a Detour
    • Using a custom image for the Macro
    • Placing a copyright into your workflow/macro
    • Placing a logo into your workflow/macro

Finally, I also created a video for those who prefer a more visual explanation

The macro has two (2) containers which isolate the macro interface tools from the IP Validation/Sorting process. The formula for the IP validation uses RegEx to see if the numbers and dots conform to the standard of: {0-255}.{0-255}.{0-255}.{0-255}

It would be easy to check for {0-999}.{0-999}.{0-999}.{0-999} with:

\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}

This problem required something more creative, like:

^((\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])\.){3}(\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])$

IP Validation

0.0.0.0 to 255.255.255.255

Regular Expression:

^((\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])\.)({3}\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])$

Breakdown:

  • ^ Beginning of text
  • (
    • (\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])
      \.)
      • \d any single Digit [0-9] :: This allows for the group to be 0 through 9
      • | OR
      • [1-9] the numbers 1 through 9 followed by :: This allows for 1 to 99
      • \d any Digit [0-9]
      • | OR
      • 1 the number 1 followed by :: This allows for the group 100 to 199
      • \d{2} any two Digits [0-9]
      • | OR
      • 2 the number 2 followed by :: This allows for the group 200 to 249
      • [0-4] the numbers 0 through 4 followed by
      • \d any Digit [0-9]
      • | OR
      • 25 the number (sequence) 25 followed by :: This allows for 250 to 255
      • [0-5] the numbers 0 through 5 followed by
      • \. A decimal point
    • {3} The above logic is repeated for 3 fields
    • (\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5]) This logic is similar to the above logic, but doesn’t include a decimal point at the end.
  • $ End of text

Here's a quick peek at the macro:

Capture.PNG

If you don't know what field name the input data will have for a given element that is REQUIRED, you can require field mapping in the macro input. This will essentially change the incoming field name to your required field. The issue is that the data is renamed on output to your name. In order to revert back to the incoming field name, there is an action tool connected both to the incoming macro input as well as to the final select tool. That's a TIP!

Please watch this short video. It explains why the TIP is so crucial.

There is a detour that allows for SORTING of the data. If the detour is checked, the records containing the IP data pass to the right/bottom and gets parsed and sorted otherwise the data flows without interruption. When sorting the numeric data (remember that this is a text field), version 11 allows you to sort in dictionary order so that you'll get the desired numeric series on output. That's a TIP!

I invite you to watch the video (less than 15 minutes) and review the macro & word documents. Please let me know your thoughts and if this article was of value to you.

Attachments
Comments
MichaelMains
7 - Meteor

This is great.  I just noticed one little thing.  In your write up, the color-coded version of your regex statement has a misplaced parenthesis. You open the second half of the statement before the {3} instead of after.  

MarqueeCrew
20 - Arcturus
20 - Arcturus

@MichaelMains,

 

Great EYE!

 

^((\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])\.){3}(\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])$
Claudio
5 - Atom

Hi,

After this validation, I need to format all records set to false in "IP validation". 
Example:

My output is: 

XXXYYYZZZAAA

XXYYYZZZAAA

 

And I need to format to:

XXX.YYY.ZZZ.AAA

XX.YYY.ZZZ.AAA

 

Can you help me ?

 

jrdepriest
7 - Meteor

There is another workflow that touches on similar issues and goes into the binary conversions behind the scenes.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Network-Math-ip-and-netmask-convert-to...

 

I've found it very useful.

 

Also, I am a 'she/her' not a 'he/him'.

 

Thanks!

Jasey