Wrangle Language

Wrangle is the domain-specific language used to build transformation recipes in Designer Cloud Enterprise Edition.

A  Wrangle recipe is a sequence of transforms applied to your dataset in order to produce your results.

• A transform is a single action applied to a dataset. For most transforms, you can pass one or more parameters to define the context (columns, rows, or conditions).
• Some parameters accept one or more functions. A function is a computational action performed on one or more columns of data in your dataset.
• Recipes are built in the Transformer Page. See Transformer Page.

When you select suggestions in the Transformer Page, your selection is converted into a Wrangle command and added to your recipe.

Wrangle vs. SQL

While there are some overlaps between  Wrangle and SQL, here are the key distinctions:

• Wrangle is a proprietary language designed for data transformation. Every supported transformation is designed to make changes to a dataset. It cannot be used to read from or write to a datastore. 
  • Users interact with  Wrangle exclusively through the Designer Cloud application. There is no direct interaction with the language.
• SQL (Structured Query Language) is designed for querying, transforming, and writing for relational datasources. It cannot be applied to file-based datasets.
  • SQL cannot be used to transform data in  Designer Cloud Enterprise Edition.

Wrangle Syntax

Wrangle transforms follow this general syntax:

(transform) param1:(expression) param2:(expression)

Common Parameters

Depending on the transform, one or more of value, col, and row parameters may be used. For example, the set transform can use all three or just value and col.

Parameter Inputs

The following types of parameter inputs may be referenced in a transform's parameters. 

Other Alteryx data types can be referenced as column references. For literal values of these data types, you can insert them into your expressions as strings. Transforms cause the resulting values to be re-inferred for their data type.

Column reference

A reference to the values stored in a column in your dataset. Columns can be referenced by the plain-text value for the column name.

Example:  value parameter references the myCol column.

derive type:single value: myCol as:'myNewCol'

Column names with spaces or special characters in a transformation must be wrapped by curly braces. 

Example: Below, srcColumn is renamed to src Column, which requires no braces because the new name is captured as a string literal:

rename type: manual mapping: [srcColumn, 'src Column']

After the column has been renamed with a space, it must be referenced in curly braces to be renamed back to its original name:

rename type: manual mapping: [{src Column},'srcColumn']

Functions

Some parameters accept functions as inputs. Where values or formulas are calculated, you can reference one of the dozens of functions available in Wrangle.

Example:

derive type:single value:MULTIPLY(3,2) as:'six'

Metadata variables

Wrangle supports the use of variable references to aspects of the source data or dataset. In the following example, the ABS function is applied to each column in a set of them using the $col reference. 

set col: val1,val2 value: ABS($col)

$col returns the value of the current row. For more information on these variables, see Source Metadata References.

Nested expressions

Individual parameters within a function can be computed expressions themselves. These nested expressions can be calculated using constants, other functions, and column references.

Example: Computes a column whose only value is ten divided by three, rounded to the nearest integer (3):

derive type:single value:ROUND(DIVIDE(10,3),0) as:'three'

Integer

A valid integer value within the accepted range of values for the Integer datatype. For more information, see Supported Data Types.

Example: Generates a column called, my13 which is the sum of the Integer values 5 and 8:

derive type:single value: (5 + 8) as:'my13'

Decimal

A valid floating point value within the accepted range of values for the Decimal datatype. For more information, see Supported Data Types.

Example: Generates a column of values that computes the approximate circumference of the values in the diameter column:

derive type:single value: (3.14159 * diameter) as: 'circumference'

Boolean

A true or false value.

Example: If the value in the order column is more than 1,000,000, then the value in the bigOrder column is true.

derive type:single value:IF(order > 1000000, true, false) as:'bigOrder'

String

A string literal value is the baseline datatype. String literals must be enclosed in single quotes.

Example: Creates a column called, StringCol containing the value myString.

derive type:single value:'myString' as:'StringCol'

Alteryx pattern

Designer Cloud Enterprise Edition supports a special syntax, which simplifies the generation of matching patterns for string values.

Patterns must be enclosed in accent marks ( `MyPattern`). For more information, see Text Matching.

Example: Extracts up to 10 values from the MyData column that match the basic pattern for social security numbers (XXX-XX-XXXX):

extract col: MyData on:`%{3}-%{2}-%{4}` limit:10

Regular expression

Regular expressions are a common standard for defining matching patterns. Regex is a very powerful tool but can be easily misconfigured.

Regular expressions must be enclosed in slashes ( /MyPattern/ ).

Example: Deletes all two-digit numbers from the qty column:

replace col: qty on: /^\d$|^\d\d$/ with: '' global: true

Datetime

A valid date or time value that matches the requirements of the Datetime datatype. See Supported Data Types.

Datetime values can be formatted with specific formatting strings. See DATEFORMAT Function.

Example: Generates a new column containing the values from the myDate column reformatted in yyyymmdd format:

derive type:single value:DATEFORMAT(myDate, 'yyyymmdd')

Array

A valid array of values matching the Array data type.

Example:

[0,1,2,3,4,5,6,7,8]

See Supported Data Types.

Example: Generates a column with the number of elements in the listed array (7):

derive type:single value: ARRAYLEN('["red", "orange", "yellow", "green", "blue", "indigo", "violet"]')

Object

A valid set of values matching the Object data type.

Example:

{"brand":"Subaru","model":"Impreza","color","green"}

See Supported Data Types.

Example: Generates separate columns for each of the specified keys in the object ( brand, model, color), containing the corresponding value for each row:

unnest col:myCol keys:'brand','model','color'

Interactions between Wrangle and the Application

1. As you build  Wrangle steps in the Transform Builder, your syntax is validated for you. You cannot add steps containing invalid syntax. 
   1. Error messages are reported back to the application, so you can make immediate modifications to correct the issue.
   2. Type-ahead support can provide guidance to the supported transforms, functions, and column references.
   3. For more information, see Transform Builder.
2. When you have entered a valid transform step, the results are previewed for you in the data grid.

   1. This preview is generated by applying the transform to the sample in the data grid. 

      NOTE: The generated output applies only to the values displayed in the data grid. The function is applied across the entire dataset only during job execution.

   2. If the previewed transform is invalid, the data grid is grayed out.
   3. For more information, see Transform Preview.
3. When you add the transform to your recipe: 
   1. It is applied to the sample in the application, and the data grid is updated to the current state.
   2. Column histograms are updated with new values and counts.
   3. Column data types may be re-inferred for affected columns.
4. Making changes:
   1. You can edit any transform step in your recipe whenever needed.
      
      1. When you edit a transform step in your recipe, the context of the data grid is changed to display the state of your data up to the point of previewing the step you're editing. 
      2. All subsequent steps are still part of the recipe, but they are not applied to the sample yet.
      3. You can insert recipe steps between existing steps.
   2. When you delete a recipe step, the state remains at the point where the step was removed.
      1. You can insert a new step if needed.
      2. When you complete your edit, select the final step of the recipe, which displays the results of all of your transform steps in the data grid. Your changes may cause some recipe steps to become invalid.
   3. See Recipe Panel.

Transforms

A  transform , or verb, is an action applied to rows or columns of your data. Transforms are the essential set of changes that you can apply to your dataset. For more information, see Transforms.

Function Categories

A function is an action that is applied to a set of values as part of a transform step. Functions can apply to the values in a transform for specific data types, such as strings, or to types of transforms, such as aggregate and window function categories. A function cannot be applied to data without a transform.

Operator Categories

An operator is a single character that represents an arithmetic function. For example, the Plus sign (+) represents the add function. 

Documentation

Documentation for Wrangle is also available through Designer Cloud Enterprise Edition. Select Help menu > Product Docs.

All Topics