This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 07-21-201608:01 PM - edited on 03-11-201909:33 AM by SydneyF
When bringing data into Alteryx a lot of users often add a select tool to check the data type and structure. Data types are very important because of the available operations/functions in tools can be adjusted to fit the data type being used!
For example, when using the basic filter tool string fields will be populated within string operators such as ‘equals’ or ‘contains’ whereas, a numeric field with have operators such as ‘>=’ or ‘isnull’.
How to check/change your data types?
Auto Field Tool
Use the auto field tool to evaluate your data set on each run and let Alteryx decide what the type and size should be. As or if your data changes the auto field tool will adjust the type and size it sets. This is especially useful when looking at speeding up data processing because Alteryx will look to assign the most efficient size and data type.
This tool will not be perfect and there are some instances where you may not want Alteryx to decide, for example a field of zip codes will appear to Alteryx to be numbers and Alteryx will set it to a number type and you will lose all your zeros at the beginning of your Massachusetts zip codes. In this case you may want to use a select tool.
Using the select tool means that Alteryx will not evaluate the data and data that doesn't fit the new, specified, type is replaced with [Null].
When considering field size make sure you are familiar with the length of the string as it will be truncated if it exceeds the length of the string.
For Numeric types the Size is not configurable, but if you are looking for precision please use Fixed Decimal.
Data that is too long is rounded (usually to 6 decimal places).
Fixed Decimals are the exception - you use the format of length of number dot followed by number of decimal places.
What happens when I bring my data into Alteryx?
Data types are applied to the entire column/field and each value within that column.
Alteryx will automatically map incoming types from Databases and Structured Files (Excel) to the best one for the type within an Alteryx table.
Strings Data Types
Strings are anything that you want to keep as it is. A String is any combination of characters, alpha-numerics and symbols. You'll also use a string type if the field is of mixed values that is has numbers in one cell and words in another.
String and Wstring are set lengths.
The V_string types are variable in length. The amount of memory reserved for processing varies.
Wstring will capture more international characters such as Japanese or Chinese.
V_String is favorable because it is flexible enough to not truncate fields but will also maximize processing time due to adjusting string size length.
Numeric Data Types
Numbers or numeric fields only contain numbers. BEWARE numeric field types and values with leading zeros (such as 01 - the zero would be dropped and the number would appear as just one). If you are dealing with Zip Codes, keep this in mind.
Byte is a small whole number.
Integer is a whole number where the 16, 32, and 64 are the size of the number (bigger numbers need more space).
Fixed decimal is typically used for currency, however, you want to watch out for numbers you will use in calculations and later roll up.
Float and Double are the most precise numbers, however, they are not good for comparison where you are trying to see if one value directly equals another.
Date & Time Data Types
The Alteryx Date Time format is specific and may require you to do some data manipulation to get your data into the Alteryx format to utilize the datetime functions within a formula tool. The formats are:
Date – YYYY-MM-DD
Date Time: YYYY-MM-DD HH:MM:SS
Sometimes dates can be difficult to work with, so we have some great tools that you can use to get your date fields into the Alteryx format.
Date Time Macro
The DateTime tool transforms date/time data to and from a variety of formats, including both expression-friendly and human readable formats.
Weighing in at 55 Alteryx tools, this macro is making its way to the top 5 most downloaded macros on the Alteryx Gallery. This macro will take away a lot of those Date Parsing headaches and make working with dates fun again!
Boolean Data Type
Boolean is simply true or false and useful for flagging data.
Conditional Flag, where 1 = true and 0 = false
Spatial Object Data Types
Spatial Objects are spatial objects like centroids and polygons used for mapping.
Alteryx can bring in Spatial Objects from data file types such as ESRI Shapefiles.
However, if you have a Latitude and Longitude in Excel you can use our Create Points tool to create Spatial Objects within Alteryx.
If you want any more information of data types please see our Alteryx Help