Alteryx Data Types 101

[JordanB]

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 efficientsize 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.

Select Tool

• Using the select tool means that Alteryx will not evaluate the data and data that doesn'tfit 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'llalso 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 toutilize the datetime functions within a formula tool. The formats are:

• Date – YYYY-MM-DD
• Date Time: YYYY-MM-DD HH:MM:SS
• Time: 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.

@AlexKo Parse Dates Macro

• Weighing in at 55Alteryx 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

[Izbiz]

Hi Jordan,

Is there a way to match the datatypes of data parsed and formatted in Alteryx to those of the table it's going to be written to in SQL db?

Would be handy as I often change datatyes in sql as it quicker when having > 100 columns and so be good to match these dtypes to new data coming in

Thanks,

Izzy

[TrishaMeis]

Any ideas why the Email tool wouldn't work for some workflows? I am able to get it to work in a new workflow and another existing workflow (workflow A), but when adding the same info in an existing workflow (workflow B) it won't run. The workflow I am adding it to has a lot of other database pulls, but it doesn't make sense to me why it isn't working.

Side note: This workflow (workflow B) also won't let me schedule it. However, I am manually running it and it still won't send an email.

Does anyone have any suggestions on what to check out for the possible error? 

[seven]

Hi JordanB, what is meant by "Float and Double are the most precise numbers"?

[Vardane]

Hi Jordan & esteemed Alteryx users,

How do the following Alteryx types: Double, Float and FixedDecimal map to Decimal, Numeric, Float and Double as defined in T-SQL ?

(background: Alteryx workflow where all operations are In-DB, the DB being on Microsoft SQL Server).

Thanks

[ruddikl]

Hi!

I have a column that has both numbers and letters, when I use the Int64 option all of the Values with Letters go away. If I use the V_String option, I then have to convert all my column using "convert to number" in excel. Is there a better way to do this??

[Nagesh_Bandaru]

Hi ruddikl,

This is not possible, as a single column would always be considered with a single datatype. otherwise, we can split the results into strings and integers by using the Filter tool and handle them separately with different outputs. then copy and paste into the excel would help you.

[VeronicaElse]

Hello,

I am new to Alteryx, quick hopefully simple question, I want add a formula if possible to add a date column plus the time column to create a new column. Is this possible? Please say yes 😕

Thanks!

Veronica Else

[FlorianC]

@VeronicaElse Hopefully you have already found a solution by now, but the answer to your question is indeed yes.

A date column (data type Date "YYYY-MM-DD") can be joined with a time column (data type Time "HH:MM:SS") in a datetime column (data type DateTime "YYYY-MM-DD HH:MM:SS") by simple concatenation as below.

[DateTime] = [Date] + " " + [Time]

This also works with string columns as long as they follow the format specified.