Filtering out the unnecessary values in a column

[dunkindonut7777]

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?

[IraWatt · Accepted 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 

[IraWatt]

@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:

[dunkindonut7777]

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

[IraWatt]

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:

[dunkindonut7777]

I want to know also how would the regex formula helps?

[IraWatt]

What do you mean by "how would the regex formula helps" ?

[dunkindonut7777]

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?

[IraWatt]

Ah okay, you could for example use:

REGEX_Match([Date], "\d+-\d+")

 \d+ means one or more digits.  

[binuacs]

@dunkindonut7777 One way of doing this is with the RegexTokenize method. you can filter out the unwanted records