Extract Values

The following example transformation extracts the word #bigdata from the column msg_LinkedIn:

msg_LinkedIn

Custom text or pattern

'#bigdata'

1

Notes:

You can generate a new column of values extracted from a source column by entering example values to match with source values. Values with similar patterns may also be matched based on your entered example value.

For more information on transformation by example, see Overview of TBE.

Within the extract transformation, you can specify literals or patterns before or after which the match is found. This method can be used to remove parts of each cell value from erroneously matching on the literal or pattern that is desired.

The following example extracts the second three-digit element of a phone number, skipping the area code:

phone_num

Custom text or pattern

`{digit}`

1

`{start}{digit}{3}\-`

You can also do pattern-based extractions using Alteryx patterns or regular expressions.

The following example extracts all words that begin with # in the msg_LinkedIn column:

msg_LinkedIn

Custom text or pattern

`\#{alphanum-underscore}+`

50

Notes:

In your pattern expressions, you can use the vertical pipe character (|) to define multiple patterns to find. The following example extracts any value from the myDate column that ends in 7 pr in 8:

myDate

`{any}+7|{any}+8`

`{end}`

You can use the vertical pipe in both Alteryx patterns and regular expressions.

You can extract the first or last set of characters from a column into a new column. In the following example, the first five characters from the ProductName column are extracted into a new product identifier column:

ProductName

First characters

5

You can change the Option value to Last characters to extract from the right side of the column value.

If you need to remove the characters that you extracted, you can use the following transformation. In this case, the first five characters, which were extracted in the previous transformation, are removed:

ProductName

RIGHT(ProductName, LEN(ProductName)-5)

You can extract values between specified index positions within a set of column values. In the following example, the text between the fifth and tenth characters in a column are extracted to a new column.

ProductName

Between two positions

5

10

You can use functions to extract values from Datetime columns. The example below extracts the year value from the myDate column:

Single row formula

YEAR(myDate)

myYear

The following functions can be used to extract values from a Datetime column, as long as the values are present in the formatted date:

You can also reformat the whole Datetime column using the DATEFORMAT function. The following reformats the column to show only the two-digit year:

myDate

DATEFORMAT(myDate, "yy")

You can extract numerical data from text values. In the following example, the first number is extracted from the address column, which would correspond to extracting the street number for the address:

address

Numbers

1

Empty values in this new column might indicate a formatting problem with the address.

Using functions, you can extract specific elements of a valid URL. The following transformation pulls the domain values from the myURL column:

Single row formula

DOMAIN(myURL)

myDomain

In some cases, the function may not return values. For example, the SUBDOMAIN function returns empty values if there is no sub-domain part of the URL.

The following functions can be used to extract values from a set of URLs:

You can extract query parameter values from an URL. The following example extracts the store_id value from the storeURL field value:

storeURL

HTTP Query strings

store_id

If your data includes sets of arrays, you can extract array elements into columns for each key, with the values written to each key column.

Suppose your restaurant dataset includes a set of characteristics in the restFeatures column in the following JSON format:

{
  "Credit": "Y",
  "Accessible": "Y",
  "Restrooms": "Y",
  "EatIn": "Y",
  "ToGo": "N",
  "AlcoholBeer": "Y",
  "AlcoholHard": "N",
  "TotalTables": "10",
  "TotalTableSeats": "36",
  "Counter": "Y",
  "CounterSeats": "8"
}

You can use the following transformation to extract the values from TotalTableSeats and CounterSeats into separate columns:

restFeatures

TotalTableSeats

CounterSeats

Selected

After the above is executed, you can perform a simple sum of the TotalTableSeats and CounterSeats columns to determine the total number of seats in the restaurant.

In some cases, your data may contain arrays of repeated key-value pairs, where each pair would exist on a separate line. Suppose you have a column called, Events, which contains date and time information about the musician described in the same row of data. The Events column might look like the following:

[{"Date":"2018-06-15","Time":"19:00"},{"Date":"2018-06-17","Time":"19:00"},{"Date":"2018-06-19","Time":"20:00"},{"Date":"2018-06-20","Time":"20:00"}]

The following transformation creates a separate row for each entry in the Events column, populating the other fields in the new rows with the data from the original row:

Events

Using Alteryx patterns, you can extract the values of the column to form a new column of arrays. The following example shows the usage of {any} pattern to extract the cell values and form a new array column.

Transformation:

product

`{any}`

`,`

Results:

In this example, you extract one or more values from a source column and assemble them in an Array column.

Suppose you need to extract the hashtags from customer tweets to another column. In such cases, you can use the {hashtag} Alteryx pattern to extract all hashtag values from a customer's tweets into a new column.

Source:

The following dataset contains customer tweets across different locations.

Transformation:

The following transformation extracts the hashtag messages from customer tweets.

customer_tweets

`{hashtag}`

Hashtag tweets

Then, the source column can be deleted.

Results: