To continue the series of Alteryx quick tips that you can use at work we look at dates this week.
Let’s imagine that the company you work for has an international presence with business both in the US and Europe. You are sent a file with some text before and after a date, however those rows have different types.
i.e. Row 1 has a date type of YYYY-MM-DD and Row 2 has a date type of MM/DD/YYYY
There are a couple of other things too.
- We want to extract the text before and after the dates
- Dates are currently strings and we would like Tableau to identify them as dates converting both types to a single format.
To do this we will go back to our friend REGEX.
We will need to parse our field, instead of replacing like we did in the previous post.
Let’s break down each output separately
- RegExOut1 – (.*)
- This will parse any text it finds before the next field
- RegExOut2 – (\d\d\d\d-\d\d-\d\d)
- \d stands for digit and we need to find 4 digits for year, followed by the hyphen separator do the same for month and day and close parenthesis to indicate the end of this field.
- RegExOut3 – (\d\d/\d\d/\d\d\d\d)
- This is the second data type. You may have noticed this symbol in between the parsing | this stands for OR. Therefore when parsing Alteryx will try and find the date type YYYY-MM-DD and if it doesn’t find it it will then look for the second date type MM/DD/YYYY
- RegExOut1 – (.*)
- This will parse any text it finds after the previous field
Once you’ve run the workflow your parsed fields should look like the table below. So far so good, we now have the text before and after the dates but we two fields for dates and we want them to be unified in a single field but also to be converted from a string to a date.
Let’s convert the strings to dates first by connecting a DateTime tool to the workflow and tell Alteryx how the date should be converted. In this case it will be MM/dd/yyyy
Second date type conversion
Your end result is now the original field, your parsed fields and two date fields for each of the date types.
We want to unify both dates in the same field, therefore we bring a formula to the workflow and write the following if statement.
Almost there, our “New Date” field is now the combination of both [Time] and [Time2].
Bring in the Select tool to remove those fields that are no longer necessary and you are done.
This is the workflow. I hope this helps you the next time you have to parse a date out of a text field as always, please leave any comments or questions below.
Thank you for reading