To create a transformation, click on the Create new job button on the My Jobs page or Use in a new job button in the Data Catalog or My Sources.

When using the Create new job button, you’ll first have to pick your data source. You can either pick an existing data source that you have already added to My Sources, or you can drop a new file at the top of your screen.

Regardless of how you create your data source, Wrangler will show you preview of the data, and you’ll be able to continue to create your transformation by pressing the Select button in the top-right corner.

To create a copy of an existing job use the Duplicate job action, which is available under the ⋮ menu on the right side of each job on the My Jobs page. The duplicated job is created with the same source, transformation steps, and target configuration as the original job. The new job is added right above the original job.

To share jobs between users, jobs can be exported and imported. To export a job, use the Export job action, which is available under the ⋮ menu on the right side of each job on the My Jobs page.

You can optionally select to include the data source and lookup files (this option applies to CSV file sources only). The export will generate a single ZIP file.

To import a job use the Import job button in the top-right corner of the My Jobs page. Drag and drop or upload a job ZIP file and optionally select if you want to overwrite the job if it already exists. The imported job will be added to the bottom of the job list.

The Test run option is available when your job writes data into a data target connector. Use this option to perform a trial run to see how many records would be written to the target interaface and how many would be rejected. If your target is configured to generate a reject file, you can also download the reject file to review further reject details. For more information on reject files, see Job run details. Note that only values from mapped columns are included in the number of rejected records and in the related reject file. For more information on target mapping, refer to the Target mapping section.

Integer columns can store whole numbers. Smallest integer value is -9223372036854775808 while the largest integer number is 9223372036854775807 (i.e., Wrangler’s integers use 64 bits and correspond to CTL long data type).

Decimal columns store decimal numbers with fixed precision of 32.10 - i.e., they can have 32 significant digits with 10 of those digits after a decimal point. The smallest decimal value is -9999999999999999999999.9999999999 and the largest value is 9999999999999999999999.9999999999.

You can store up to 10 decimal places in decimal columns. Decimal places after 10th decimal digit will be ignored - i.e. writing 1.1234567890555 will result in 1.123456789.

Decimal in Wrangler corresponds to CTL type decimal with length set to 32 and scale set to 10.

Date columns in Wrangler store both date and time - there are no separate types to store just the date or just the time. The dates are stored with millisecond precision and you can use date formatting options to configure what the dates look like.

Date columns can store any date between (approximately) 290 million years ago to 290 million years in the future.

When entering dates in formulas or in target mapping, dates need to be entered in a specific format. See Working with dates for more information.

String columns represent text data in Wrangler. They can store text in any language (UTF-16 is used to store the data). You can store strings up to 2.1 billion characters long (less for complex languages like Chinese or Japanese).

Data containing multi-line strings will be displayed with a preview of the first line followed by ellipses (…​). To view the full content, simply hover your cursor over the value.

Boolean columns store results of logical expressions and can store a "yes" or "no" value (shown as true or false).

Besides storing values, each column in Wrangler can also store empty value (sometimes called null values). Empty values are shown as No value in the data preview:

Note the String column which shows two different kinds of empty value - a null (shown as No value) and empty string (shown as cell with no text inside it).

Empty values must be handled separately if you’d like to perform operations on them. Steps in Wrangler will usually ignore empty values but in some cases may return errors. See documentation for each step to see how it handles empty values.

Integer columns offer the following options for formatting:

Format defines the pattern that tells the formatter how many digits and how to write them.

Thousands separator allows you to select character to use between groups when the pattern specifies a format that groups digits (e.g., it allows you to configure whether to write larger numbers as "1 234" or "1,234").

Pattern uses a set of special characters that are interpreted by the formatter:

Examples: consider numbers -1234 and 1234 and the following patterns with English (United states) setting for Thousands separator (i.e., a comma is used as separator character):

For more information about number formatting options see Numeric formats.

Decimal columns offer following formatting options:

Format defines the pattern that tells the formatter how many digits and how to write them. Thousands separator / Decimal mark parameter allows you to select character to use between groups and as a decimal separator.

Pattern uses a set of special characters that are interpreted by the formatter:

Examples: let’s consider two examples: numbers 1234.05 and -1234.05 with language set to English (United states):

For more information about number formatting options see Numeric formats.

Date columns provide three parameters you can configure:

Format allows you to configure pattern that can be used to format date and time elements, Language allows you to select language and Timezone provides options to configure the timezone in which the dates will be interpreted.

Many different special characters are allowed in the formatting pattern. Following table provides a summary of the most common ones:

Using a special character multiple times in a row (like MM) will left pad the numeric values with zero. For date elements that have names (like months), using three or more characters will spell out the whole value in selected language (e.g., for February and US English locale, MMM will produce value "Feb" while MMMM will produce "February").

See more details in For more information about number formatting options see Date and time formats.

Examples: let’s imagine date/time value of 27th February 2023, 16:35:50.456:

For more information about possible patterns and formatting options see full documentation in Date and time formats.

You’ll often need to reference various columns from your data set in your formulas. To refer to a column, use its technical name, an identifier (unique name) starting with a dollar sign ($).

Technical column names cannot contain special characters like spaces, commas, parentheses and so on. Wrangler creates them for you from column names by removing those characters and replacing them with underscore. Technical column names are case-sensitive - a column called $AMOUNT is different from $amount.

If you type a wrong column name (a name that does not exist in your data set), you’ll get an error like this:

You can easily find and copy the technical name of a column by hovering your mouse over the column header in the data preview and clicking on Copy. Alternatively, you can take advantage of the autocomplete feature to quickly display the list of all available technical names or formulas.

Take advatage of the autocomplete feature in Formula editor to help you quickly find the desired column names or functions:

To add 20% VAT to a price column, you first check the technical name of the column by hovering mouse over the Price (no VAT) column (technical name is $Price_no_VAT) and then use the Calculate formula step with the following formula:

The result might look like this:

You can use several operators when working with your formulas to compute values or to work with text data:

Operator priorities work as expected - multiplication, division and division remainder have higher priority than addition or subtraction. You can also use parentheses to group your operations.

Examples:

The operators all work on all numeric types - integers as well as decimals. The only exception is the + operator which also works on string and can be used to concatenate strings like this:

Note that it is also possible to concatenate string with another type (e.g., "VAT is " + $vatPercent) but you need to be careful about data formatting. Default formatting will be applied when converting to string. For greater control of the formatting you can use CTL string conversion functions like date2str and so on (the functions you’d most likely use all end with 2str suffix).

If you’d like to compare various values when writing conditions in Filter rows based on formula step, you can use the following operators:

These operators can be applied to any data type but both sides of the comparison must be of similar or same data type - i.e., you can compare integer and decimal but you cannot directly compare integer and string.

The operators always return boolean value so they can be used directly as a condition.

Note that the last two operators can only be applied to string values.

Examples:

Most common use for these operators is in the Filter rows based on formula step but they can also be used in Calculate formula step.

Logical operators allow you to connect multiple conditions together to create more complex conditions. They can only be applied to boolean values and will produce another boolean value.

Examples:

These operators are most commonly used in Filter rows based on formula step where they are useful when defining more complex conditions.

Your data may not always match the expected format. For example, you may get text in numeric column, wrongly formatted dates etc. Wrangler can detect errors like these and will allow you to work with the data even if it does not match the expected data type or format. Additionally, you can use Validation steps to ensure that your data matches your expectations and add customized error messages to data that does not meet those expectations.

If a row which contains any kind of an error gets through the transformation all the way to the data target, it will be rejected. Rejected records are collected in a file that allows you to inspect error details even in large data sets. See below for additional details about reject file format.

For data errors you can see that the value read from source is retained even if it does not fit into expected format - for example, the Date paid column in the above screenshot contains values like n/a or incorrectly formatted dates. Wrangler allows you to access the original (invalid) value in variety of steps so that you can attempt to fix the issue in your transformation.

To learn how to fix errors in data, read more in Fixing errors and Fixing data errors sections below.

Some values might be invalid even if they are of correct type and have correct format. For example, age of a person cannot be negative number, invoice amount cannot be empty and so on. Wrangler offers several Validation steps that allow you to validate your your data and flag values that do not match the requirements for given column.

All values that are flagged as invalid - validation errors - are shown just like any other error in Wrangler with red cells in data preview.

These errors happen when you run a step and it encounters a value it cannot handle. For example, this can happen if you try to apply mathematical step to an empty value, if you divide by zero, or use invalid length in string function etc.

Just as before, Wrangler will show the errors in the data preview and will allow you to work with all other records even if some data is in error.

Notice that these errors do not show any value in the cell since the calculation of the new value could not finish. Instead, they are shown as Error. To fix this type of error, you usually need to find the step that causes it and either fix the data before that step so that the steps does not fail anymore or modify the step settings.

To learn how to fix errors raised during step execution, read more in Fixing errors and Fixing step execution errors sections below.

These errors happen during design time and are detected by Wrangler as you work on your transformation. These errors generally mean that the step cannot be executed at all and Wrangler transformation cannot progress past this step.

For example, the following screenshot shows a step configured to use a column that is no longer part of the data set:

To learn how to fix these errors, read more in Fixing step configuration errors section.

Since Wrangler allows you to continue working with your data even if there are errors in your data, it may happen that some rows that contain errors can get all the way to the end of the transformation into the data target.

Since data targets may not be able to write the malformed or erroneous data into their output, they allow you to configure what to do with rows that have errors. You can pick from two options:

Both of the options above are available in the target configuration dialog. Read more about this in CSV data target configuration and Excel data target configuration.

These errors can often be prevented by changing the column type to a more permissive type (e.g., integer to decimal etc.).

If changing type is not viable, you can attempt to parse or interpret the original value in some other way to recover from the error. This is possible since Wrangler keeps the original invalid value and allows you to access that value in error fixing steps like Replace errors step.

For example, common error is that you receive dates with incorrect format like in the Date paid column on the following screenshot:

To fix these, you can use Replace errors step and parse the incorrectly formatted date with different format by using a formula str2date($Date_paid, "d.M.yyyy"). This will result in the following output where the incorrectly formatted dates are fixed:

One of the most common ways of getting step execution errors is to use Calculate formula step and forgetting to deal with an edge case in the data. Typically this means not handling No value cells in your formulas or values that are outside of the range of valid inputs for your formula.

For example, math formulas typically do not handle null (No value). Consider a simple formula like this:

This formula applies VAT specified in $vatPercent column to amount specified in $amount column. If either of those values is null, the formula will fail.

To fix this, you can either decide not to run the step at all and return a default value. To do that, you can use if function. The following formula tests for null values of its inputs and only runs if the inputs are not null:

Alternatively, we may decide that if there is no VAT, we use a default value such as 21% (we are not handling null value of $amount column in this formula):

Another option for dealing with such errors is to fix the data before it gets to the step that fails.

For example, to get the same effect as the the last formula above, you can use Replace empty values step configured to set $vatPercent to 21 if it is empty. The computation of the amount with VAT can then remain in its original form since that step will guarantee that there are no null values in the $vatPercent column.

If the above options are not possible, rows with errors can be removed from the data set by using filtering steps like Remove rows with errors or Filter rows based on formula.

These errors must be fixed in the step that is failing. Wrangler will run your job as far as it can and will stop on the first step that failed. For example, consider the following job:

In this job we are trying to sort data based on column that is no longer available in the data set. This can happen for example if your data source is a CSV file and it no longer contains the column you used or if the column has been renamed.

You can see that the Wrangler will try to run the job as far as it can and stops on the invalid step. The only way to fix this is to either disable the step (or even remove it) or update the step settings to use a different column (if the column was renamed in the source data set).

The My Jobs page will show you the status of your jobs - it shows each job on a single row and displays status of the last run of the job.

Jobs can have one of the following statuses:

After the job finished successfully, you will be able to download its results directly from My Jobs page by clicking on the Download result button. This will download the output file - Excel or CSV - to your computer.

For jobs that produced reject files or ended in an error, you can view additional details in the Last run status dialog. To open the dialog, click on the rejected rows status message (orange text) or on the failed status (red text) on the My Jobs page.

The dialog allows you to download result as well as reject files via Download result and Download reject file buttons in the left part of the dialog.

In the right half of the dialog, you can see additional details about the errors that occurred during the job run. The overview table supports two different ways of showing th error summary:

If the job ended in an error, the dialog will allow you to see additional details about the error:

Selection of data types available to use in Wrangler is more limited than types in CTL and in metadata.

The following table summarizes the types supported in Wrangler columns compared to CTL and metadata column types.