Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Cleansing date field

nervo1234
6 - Meteoroid

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
6 REPLIES 6
JordanB
Alteryx
Alteryx

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

 

 

nervo1234
6 - Meteoroid

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

nervo1234
6 - Meteoroid

Figured this out for myself.  Using the custom field in Date/Time function - %d-%B-yyyy

 

Thanks for the help on RegEx formula.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@nervo1234,

 

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

 

Capture3.PNG

 

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.

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
nervo1234
6 - Meteoroid

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

alex
11 - Bolide

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

Labels