How to create a date combining different fields in Alteryx

Let’s take a look at how to create a date combining different fields in Alteryx. It is very common to handle databases that have date fields split into several columns (i.e., year, month and day) instead of a single field providing the complete information stored as DateTime format.

We can use a specific formula in Alteryx to create a DateTime field from several split columns.

The starting database should appear as shown below

The aim is to get a new field ‘Date’ in a datetime format resulting from the combination of the data available in the three starting fields.

DateTimeParse

DateTimeParse is a very useful formula available in Alteryx that is used in order to combine string fields to create directly a new datetime field.

1. Connect a formula tool directly to your workflow
2. In the configuration panel create a new column typing the following expression with ‘Date’ as output data type

Syntax used

DateTimeParse(dt, f, l) converts a date string with the specified format, in a specified language, to the standard ISO format (yyyy-mm-dd HH:MM:SS).
Parameters:

• dt: Date-time string data expressed as a selected column or a date-time string between quotes. The incoming data must be a String data type.
• f: The format of the incoming date string data that you are converting, expressed in a format string between quotes.
• l: (Optional) The language of the incoming date string data that you are converting. The language parameter defaults to your selected Designer Cloud language.

DateTimeParse(ToString([Year])+'-'+ToString([Month])+'-'+ToString([Day]),'%Y-%m-%d')

In our dt we have defined our string to convert combining the three original fields

ToString([Year]) + '-' + ToString([Month]) + '-' + ToString([Day])

It is used to create the string to convert in the datetime format

Tostring() is used because DateTimeParse() accepts only string data

In our f we have defined the format to use to convert the string in a datetime format.

'%Y-%m-%d'

It is the format we want for the output date

The ending database should appear as shown below

As you can see the three original fields have been combined to create a new field in a DateTime data type.

We have seen how to create a datetime field starting from the data split in three different columns.

Here the most basic syntax for datetime formats is reported:
%a Abbreviated Weekday Name (“Mon”)
%A Full Weekday Name (“Monday”)
%b Abbreviated Month Name (“Sep”)
%B Full Month Name (“September”)
%m Month in two digit (“12”)
%d Day of the Month (“01”)
%y Last two digits of the year (“16”)
%Y All four digits of the year (“2016”)

Continue reading

Check out: How to Parse fields using RegEx in Alteryx