What is the best way to filter different Date formats in a column?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
Can someone please help me on this - A column with different Date formats needs to be filtered. How do I do that & convert it to a right right DateTime format?
Using the '/', '-' or '.' symbols can be used for the "Contain" function in the filter tool, but I want something dynamic rather than hard coded.
Attached sample data.
Thank you!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ShankerV the expected output should be Date format "2021-10-21". Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@AKPWZ Since the date fields are in different formats the only option is to identify the patterns using the symbols you mentioned above or using Regex function
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @AKPWZ
As @binuacs says - the most common way to do this is with Regular Expressions. Regular expressions (regex) are a pattern matching framework used across many different software languages and they have been around for ages - they are a little quirky to learn.
The good news is that there's a good bit of training in the Academy (under Parsing I believe).
The general pattern that @binuacs is pointing to is:
- if it looks like 4 digits; followed by a dash; followed by 2 digits - then apply 1 method. In regex this is written as \d{4}\-\d{2} since \d is a digit and {4} is the number of the prev pattern you are looking for
- if it looks like 4 digits followed by a slash followed by ...
You can do this in a formula, you could also add a bunch of filters, where each filter just looks for one of these patterns using Regex Match.
Regex is a very good skill to have - and with Chat GPT and other similar tech - you can now learn regex very quickly - and my go-to for regex is http://regex101.com
Good luck - hope this helps
Sean
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @SeanAdams thank you so much for encouraging me.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I leveraged the above examples to expand upon converting different formats to conform to the Alteryx standard. It doesn't yet validate that the date is valid. I had a file that had multiple formats (m/d/yy, mm/dd/yyyy, yyyy-mm-dd, etc). I hope this helps someone!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@JerrySmith Made a few changes in your workflow and attached
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @binuacs thank you so much for the update.
Just wanted to know one more thing that what change should we make in the RegEx to get the output as in DateTime format?
Like this, 2023-10-03 19:23:40
Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@AKPWZ Change the data type to DateTime, in the above example it was a V_WString
