Page tree

Trifacta Dataprep



Contents:

If you licensed Dataprep by Trifacta before Oct. 14, 2020, you are using the Dataprep by Trifacta Legacy product edition. On October 14, 2022, this product edition will be decommissioned by Google and will be no longer available for use. Current customers of this product edition are encouraged to transition to one of the product editions hosted by Trifacta. See Product Editions.

   

This examples illustrates how you can convert IP addresses to numeric values for purposes of comparison and sorting.

Functions:

ItemDescription
IPTOINT Function Computes an integer value for a four-octet internet protocol (IP) address. Source value must be a valid IP address or a column reference to IP addresses.
IPFROMINT Function Computes a four-octet internet protocol (IP) address from a 32-bit integer input.

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:

Transformation Name Replace text or pattern
Parameter: Column IpAddr
Parameter: Find `http%?://`
Parameter: Replace with ''

NOTE: The %? Pattern 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:

Transformation Name Replace text or pattern
Parameter: Column IpAddr
Parameter: Find `1.2.3[end]`
Parameter: Replace with '1.2.3.0'
Parameter: Match all occurrences true

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

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula IPTOINT(IpAddr)
Parameter: New column name 'ip_as_int'

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:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula IPFROMINT(ip_as_int)
Parameter: New column name 'ip_check'

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

See Also for EXAMPLE - IPTOINT Function:

This page has no comments.