Cleansing date field

Hi All,

I'm having some difficulty cleansing date fields, trying to use Left or Right, but because I want to extract the middle part of the field, I don't think these are the best functions to use.

I would like to cut "HELLO ~" and "-9:52" - from the left and right of the field, so I am left with the date "11-Nov-2011"

I'd appreciate any help - thank you.

For example:

HELLO~11-Nov-2011-9:52

GOODBYE~21-Mar-2013-11:27

Workflow

Tips and Tricks

Date Time

Alteryx Designer

Expression

Accepted answers

JordanB

Hi @nervo1234

This is where RegEx can be very useful. I have attached an example workflow which parses the dates out of the string.

It is useful to think of RegEx as pattern matching. In your example if you look at the string you could see the following pattern:

One of more digits (11) then '-' with word characters (Nov) then '-' and 4 digits (2011)

If you want to understand RegEx a little more there are one tool examples in the designer under Help>>Sample Workflows>>One tool examples>>Parse>>RegEx

Best,

Jordan Barker

Solutions Consultant

Date Parse.yxmd

MarqueeCrew

@nervo1234,

Here is an alternative date cleansing module that doesn't use RegEx. @JordanB showed you one solution, here is another:

In the formula tool I have 3 formulas. The first replaces the '~' with a space. The second replaces all dashes with a space. The reason why I made these replacements was so that I could use the GetWord() function.

GetWord(String, n)
Returns the Nth (0-based) word in the String. Words are defined as a collection of characters separated by a space. 0-based index, means the first word is at the 0 position.

Example

GetWord("Basic Variables Households", 0) returns "Basic"
GetWord("Basic Variables Households", 1) returns "Variables"

Now I can use the following formula to parse the field into a date:

DateTimeParse(
	getword([Field1],1) + 
	getword([Field1],2) + 
	getword([Field1],3),
		"%d%b%Y")

We're working with "HELLO 11 Nov 2011 9:52":

Word 0 = HELLO

Word 1 = 11

Word 2 = Nov

Word 3 = 2011

Word 4 = 9:52

The DateTimeParse will use %d as the days, %b as the month abbreviation and %Y as the 4-digit year.

Cheers,

Mark

Cleansing Date.yxmd

All comments

JordanB

Hi @nervo1234

This is where RegEx can be very useful. I have attached an example workflow which parses the dates out of the string.

It is useful to think of RegEx as pattern matching. In your example if you look at the string you could see the following pattern:

One of more digits (11) then '-' with word characters (Nov) then '-' and 4 digits (2011)

If you want to understand RegEx a little more there are one tool examples in the designer under Help>>Sample Workflows>>One tool examples>>Parse>>RegEx

Best,

Jordan Barker

Solutions Consultant

Date Parse.yxmd

nervo1234

Hi Jordan,

This is fantastic, really appreciate the quick response.

Now I have RegExOut1 into an acceptable format "21-Jun-2011" - I'd like to convert this into a Date/Time format using the Date/Time tool, although struggling to find an appropriate format to convert this into, even using the "custom" dimension.

Thanks in advance

MarqueeCrew

@nervo1234,

Here is an alternative date cleansing module that doesn't use RegEx. @JordanB showed you one solution, here is another:

GetWord(String, n)
Returns the Nth (0-based) word in the String. Words are defined as a collection of characters separated by a space. 0-based index, means the first word is at the 0 position.

Example

GetWord("Basic Variables Households", 0) returns "Basic"
GetWord("Basic Variables Households", 1) returns "Variables"

Now I can use the following formula to parse the field into a date:

DateTimeParse(
	getword([Field1],1) + 
	getword([Field1],2) + 
	getword([Field1],3),
		"%d%b%Y")

We're working with "HELLO 11 Nov 2011 9:52":

Word 0 = HELLO

Word 1 = 11

Word 2 = Nov

Word 3 = 2011

Word 4 = 9:52

The DateTimeParse will use %d as the days, %b as the month abbreviation and %Y as the 4-digit year.

Cheers,

Mark

Cleansing Date.yxmd

nervo1234

Thank you Mark - this is why I love Alteryx, such a fantastic user community

alex

Yet another way - in case GOODBYE turns to GOOD NIGHT.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703