Hi I have a data here that has a column and contains values with different format. I want to filter out those non-dates format. Here is my sample date:
Date |
01-Apr-20 |
01-Feb-20 |
01-Jan-20 |
01-Jul-21 |
25906-1 |
25906-10 |
25906-11 |
25906-12 |
25906-13 |
Expected output:
Date |
01-Apr-20 |
01-Feb-20 |
01-Jan-20 |
How can I retain these dates values in just a formula. Can you help me with this one please?
Solved! Go to Solution.
Hey @dunkindonut7777,
There are loads of ways to do this. If you want just one formula you could use a Regex Mactch formula like this:
REGEX_Match([Date], "\d{2}-\w{3}-\d{2}")
This will look for 2 digits then a dash 3 letters a dash and then 2 numbers
The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...
If you want to learn more about Regex the community has some really quick interactive videos on getting to grips with it here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20...
Any questions or issues please ask
Ira Watt
Technical Consultant
Watt@Bulien.com
@dunkindonut7777 A simpler and more efficient way would be to just convert your Date column to a date data type using the datetime tool and then filter off the rows which did not convert:
Hi thanks for this. It works. How about we do the reverse one? Like I want to retain those non-dates values?
Expected Output
Date |
25906-1 |
25906-10 |
25906-11 |
25906-12 |
25906-13 |
The Filter Tool has Two outputs, the T (true) shows the data where condition is met and the F (false) shows the ones which did not match. Check the F for that information:
I want to know also how would the regex formula helps?
What do you mean by "how would the regex formula helps" ?
I mean I want to know also how these non-dates could be formulated in regex or how to form it using use a Regex Match formula?
Ah okay, you could for example use:
REGEX_Match([Date], "\d+-\d+")
\d+ means one or more digits.
@dunkindonut7777 One way of doing this is with the RegexTokenize method. you can filter out the unwanted records