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 |
Solved! Go to Solution.
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
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
Figured this out for myself. Using the custom field in Date/Time function - %d-%B-yyyy
Thanks for the help on RegEx formula.
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
Thank you Mark - this is why I love Alteryx, such a fantastic user community :)
Yet another way - in case GOODBYE turns to GOOD NIGHT.