04-22-2017 04:08 PM - edited 07-23-2021 09:37 AM
@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:
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])$
0.0.0.0 to 255.255.255.255
^((\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])$
Here's a quick peek at the macro:
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.
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.
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])$
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 ?
There is another workflow that touches on similar issues and goes into the binary conversions behind the scenes.
I've found it very useful.
Also, I am a 'she/her' not a 'he/him'.
Thanks!
Jasey