is the domain-specific language used to build transformation recipes in .
A recipe is a sequence of transforms applied to your dataset in order to produce your results.
When you select suggestions in the Transformer Page, your selection is converted into a command and added to your recipe.
Tip: Where possible, you should make selections in the data grid to build transform steps. These selections prompt a series of cards to be displayed. You can select different cards to specify a basic transform for your selected data, choose a variant of that transform, and then modify the underlying recipe as necessary. For more information, see Overview of Predictive Transformation.
For more information on the suggestion cards, see Selection Details Panel.
Some complex transforms, such as joins and unions, must be created through dedicated screens. See Transformer Page.
transforms follow this general syntax:
(transform) param1:(expression) param2:(expression)
A transform (or verb) is a single keyword that identifies the type of change you are applying to your dataset.
The other elements in each step are contextual parameters for the transform. Some transforms do not require parameters.
Additional parameters may be optional or required for any transform.
Depending on the transform, one or more of
row parameters may be used. For example, the
set transform can use all three or just
When present, the
An expression can contain combinations of the following:
When present, the
Some transforms may support multiple columns as a list, as a range of columns (e.g.,
|When present, the |
For aggregating transforms, such as
|For aggregating transforms, such as |
The following types of parameter inputs may be referenced in a transform's parameters.
Other 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.
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.
value parameter references the
Column names with spaces or special characters in a transformation must be wrapped by curly braces.
srcColumn is renamed to
src Column, which requires no braces because the new name is captured as a string literal:
NOTE: Current column names that have a space in them must be bracketed in curly braces. The above column name reference is the following:
Some parameters accept functions as inputs. Where values or formulas are calculated, you can reference one of the dozens of functions available in .
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 returns the value of the current row. For more information on these variables, see Source Metadata References.
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):
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
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
Example: If the value in the
order column is more than 1,000,000, then the value in the
bigOrder column is
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
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 (
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 (
Example: Deletes all two-digit numbers from the
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
A valid array of values matching the Array data type.
See Supported Data Types.
Example: Generates a column with the number of elements in the listed array (
A valid set of values matching the Object data type.
See Supported Data Types.
Example: Generates separate columns for each of the specified keys in the object (
color), containing the corresponding value for each row:
This preview is generated by applying the transform to the sample in the data grid.
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.
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.
|These functions are used to perform aggregation calculations on your data, such as sum, mean, and standard deviation.|
|Comparison Functions||Comparison functions enable evaluation between two data elements, which are typically nested (Object or Array) elements.|
|Math Functions||Perform computations on your data using a variety of math functions and numeric operators.|
|Trigonometry Functions||Calculate standard trigonometry functions as well as arc versions of them.|
|Date Functions||Use these functions to extract data from or perform operations on objects of Datetime data type.|
|String Functions||Manipulate strings, including finding sub-strings within a string.|
|Nested Functions||These functions are designed specifically to assist in wrangling nested data, such as Objects, Arrays, or JSON elements.|
|Type Functions||Use the Type functions to identify valid, missing, mismatched, and null values.|
|Window Functions||The Window functions enable you to perform calculations on relative windows of data within your dataset.|
|Other Functions||Miscellaneous functions that do not fit into the other categories|
An operator is a single character that represents an arithmetic function. For example, the Plus sign (
+) represents the add function.
|Logical Operators||and, or, and not operators|
|Numeric Operators||Add, subtract, multiply, and divide|
|Comparison Operators||Compare two values with greater than, equals, not equals, and less than operators|
|Ternary Operators||Use ternary operators to create if/then/else logic in your transforms.|
Documentation for is also available through . Select Help menu > Documentation.
Tip: When searching for examples of functions, try using the following form for your search terms within the Product Documentation site: