Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0762

This examples illustrates how you can convert IP addresses to numeric values for purposes of comparison and sorting. This example illustrates the following functions:

  • IPTOINT - converts an IP address to an integer value according to a formula. See IPTOINT Function.
  • IPFROMINT - converts an integer value back to an IP address according to formula. See IPFROMINT Function.

Source:

Your dataset includes the following values for IP addresses:

IpAddr
192.0.0.1
10.10.10.10
1.2.3.4
1.2.3
http://12.13.14.15
https://16.17.18.19

Transformation:

When the above data is imported, the application initially types the column as URL values, due to the presence of the http:// and https:// protocol identifiers. Select the IP Address data type for the column. The last three values are listed as mismatched values. You can fix the issues with the last two entries by applying the following transform, which matches on both http:// and https:// strings:

D trans
RawWrangletrue
p03Value''
Typestep
WrangleTextreplacepattern col:IpAddr with:'' on:`http%?://`
p01NameColumn
p01ValueIpAddr
p02NameFind
p02Value`http%?://`
p03NameReplace with
SearchTermReplace text or pattern

Info

NOTE: The %?

D s item
itempattern
rtrue
matches zero or one time on any character, which enables the matching on both variants of the protocol identifier.

Now, only the 1.2.3 value is mismatched. Perhaps you know that there is a missing zero at the end of it. To add it back, you can do the following:

D trans
RawWrangletrue
p03Value'1.2.3.0'
Typestep
WrangleTextreplacepatterns col: IpAddr on: `1.2.3{end}` with: '1.2.3.0' global: true
p01NameColumn
p01ValueIpAddr
p02NameFind
p02Value`1.2.3[end]`
p03NameReplace with
p04Valuetrue
p04NameMatch all occurrences
SearchTermReplace text or pattern

All values in the column should be valid for the IP Address data type. To convert these values to their integer equivalents:

D trans
RawWrangletrue
p03Value'ip_as_int'
Typestep
WrangleTextderive type:single value:IPTOINT(IpAddr) as:'ip_as_int'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueIPTOINT(IpAddr)
p03NameNew column name
SearchTermNew formula

You can now manipulate the data based on this numeric key. To convert the integer values back to IP addresses for checking purposes, use the following:

D trans
RawWrangletrue
p03Value'ip_check'
Typestep
WrangleTextderive type:single value:IPFROMINT(ip_as_int) as:'ip_check'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueIPFROMINT(ip_as_int)
p03NameNew column name
SearchTermNew formula

Results:

Xip_as_intip_check
192.0.0.13221225473192.0.0.1
10.10.10.1016843009010.10.10.10
1.2.3.4169090601.2.3.4
1.2.3.0169090561.2.3.0
12.13.14.1520218215912.13.14.15
16.17.18.1926955419516.17.18.19