Understanding and Changing Data Types in Alteryx

Working with Data Types is a fundamental skill in Alteryx. They determine how Alteryx interprets each column and which operations can be applied to it. To change data types, you can either manually update them with a Select Tool, auto-detect them with an Auto-Field Tool, or temporarily change them with formulas.

Alteryx Data Types determine how operations are performed, can impact calculations, cause errors, or how results are presented.

The data type needed is typically determined by the operation you are trying to apply to it. When an operation doesn’t make sense for a given data type, such as adding a number and a text field together the results are an error. Data Types can be updated a number of different ways and will largely depend on how many columns you need to update at one time, and whether the changes are permanent or for temporary for a single formula.

Let’s dive in!

Understanding Data Types in Alteryx

Data Types play an important role in Alteryx and many other data transformation systems. They tell the software how to apply formulas and transformations to each column. Every column or field is assigned to one of three general data types, String (Text), Numeric, or Date with more granular options available in each category.

For example, two numeric columns can be added together with a + sign using a Formula Tool where two String fields can be concatenated together with the same + symbol. However, a mismatch between a numeric field and a string field will result in a calculation error.

When people are new to Alteryx, we recommend using a V_WString for text and a Double for numeric data for simplicity. Both have the fewest number of limitations on the number of characters that a field can hold, and rounding will generally work more similar to how it would in Excel. After gaining experience with the platform, adjusting data types to the smallest required type can help improve workflow performance.

Changing Data Types with the Select Tool

The Select Tool in the Preparation section of the Alteryx Toolbar is one of the most widely used tools in Alteryx. To Change the Data Type of a field, add a Select Tool to your workflow, then use the drop-down menu to the right of a field name to change the data type going out of the tool.

The picture below shows the Type Column where all values are shown as a V_String. The data types you see will depend on the assigned data type going into the Select Tool.

Screenshot of changing data types using an Alteryx Select Tool

You can also update the field name, change the order, and drop columns using the Select Tool making it highly versatile.

Forcing Data Types with a Select Tool

When working with incoming data where the data types can change, such as Microsoft Excel .xlsx file types, it can be beneficial to force a data type. For the Forced Data Type option to appear, you may have to add two select tools in a row. The first assigns a data type, the second forces it.

Forcing data types with an Alteryx Select Tool

If the incoming data in the screenshot above was already a Double data type, you would only need one tool and the Double: Forced option would appear. Because our starting data was a V_String, we changed it to Double with the first select tool, then told Alteryx to Force Double with the second Select Tool.

Changing Data Types with Embedded Select Tools

There are a number of tools in Alteryx that allow you to update data types. One example is the Join Tool. The configuration of a Join Tool includes an embedded Select Tool where the options available match the Select Tool. Be aware that the changes made in an embedded select tool will only impact data going out of a tool. In the case of a Join, only the J output will receive the new data types.

Screenshot of an embedded select tool inside of an Alteryx Join Tool

If you receive a data type mis-match error when using a Join Tool, you will need to add a Select Tool before the Join Tool and update the data type before a Join is attempted.

Auto Detect Data Types with an Auto Field Tool

The Auto Field Tool in the Preparation section of Alteryx can be used to automatically determine and assign data types to incoming data. The tool samples the data and makes its best guess on the smallest appropriate data type for each field in the data set.

Using an Auto Field tool to automatically determine data types in Alteryx

Because the Auto Field Tool selects the smallest data type, in terms of how much memory each field takes up makes it a great way to optimize large data sets. When fields take up less memory, more of a workflow can be processed in memory making the workflow run faster.

Auto Field Tools are also a great starting point for importing large CSV files where every field is read into Alteryx as a string data type.

Temporarily Convert Date Types in Formulas

When you only need to temporarily change how Alteryx asses a data type, use a ToString, ToNumber, or ToDate formula to wrap a field or part of a longer formula. These formulas tell Alteryx how to temporarily assess the data type of formula.

In the example below, we are updating a string field which normally would error out when applying a numeric calculation to it. First, we convert the “Sales” column to a number then we can multiply it by 1,000 all which keeping the field data type as V_String.

Using a ToNumber formula to temporarily change a data type inside of a formula in Alteryx

Temporary changes to data types can be extremely useful, and helps you maintain a cleaner workflow by not having to add multiple Select Tools to change a field’s data type, then change it back.

Updating Data Types with Multi-Field Formulas

Unlike the regular Formula Tool in Alteryx, you can use a Multi-Field Formula to change an output data type. The Multi-Field formula is typically used to apply the same formula to multiple columns of data in a single tool, but it can be used on a single column at a time like a regular Formula Tool as well.

Using a multi-field tool to change the output data type in an Alteryx formula

As you can see, we used the same formula in the Expression section as we did in the previous example, but have an option to “Change Output Type to” in the Multi-Field Tool configuration options.

Summary of Alteryx Data Types

The following table provides a summary of the different data types that are available in Alteryx. Our go to data types are Double and V_WString because they’re the most flexible, but it’s worth acquainting yourself with more nuances of each data type if you’re working with large datasets or numeric precision in calculations are a concern.

Data TypeSub-TypeDescriptionUnique Features
StringFixed LengthLatin-1 string with a specified length up to 8192 characters.Efficient for strings of nearly constant length.
WStringWide String that accepts any Unicode character, limited to 8192 characters.Supports a wide range of characters including non-Latin.
V_StringVariable length Latin-1 string.Length adjusts to fit the string, efficient storage.
V_WStringVariable length Wide String that can accept any character.Adjusts in length, supports all characters.
NumericByte8-bit positive whole number (0-255).Small range of whole numbers.
Int162-byte integer (-32,768 to 32,767).Small to medium range integers.
Int324-byte integer (-2,147,483,648 to 2,147,483,647).Large range of integers.
Int648-byte integer (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807).Very large range of integers.
Fixed DecimalNumeric with decimal, adjustable length up to 50 (inclusive of point and sign).Precision adjustment, unique among numerics.
FloatSingle-precision floating-point, 4 bytes.Represents a wide range of values with 7 digits precision, saves memory in arrays.
DoubleDouble-precision floating-point, 8 bytes.Wide range of values with 15 digits precision, default for decimals.
DateTimeDateString in “yyyy-mm-dd” format.Specific format for dates.
TimeString in “HH:MM:SS” format, up to 27 characters for precision.Precise time measurements.
DateTimeString in “yyyy-mm-dd HH:MM:SS” format, up to 38 characters for precision.Combines date and time with precision.
BooleanBoolTrue or False values.Binary logic, simple true/false conditions.
SpatialSpatialObjAssociates a spatial object (point, line, polyline, polygon) with a data record.Enables geographical data analysis.

Conclusion

Data Types play an important role in Alteryx and other computer systems. They tell the computer how to interpret a column of data and how to apply different operations. Numbers can be added together, text can be concatenated but you can’t add numbers and text together without receiving an error.

Beyond avoiding various errors, Alteryx will use differing amounts of memory depending on the data type assigned to a column. The less memory used, the faster a workflow should run. We generally don’t worry about it with moderate sized data sets as the performance impact is usually negligible.

When in doubt, default to a Double or a V_WString in most use cases, but be at least somewhat familiar with the other options available. They can have a big impact on data being truncated and numeric rounding.

Scroll to Top