Challenge #4: Date Parsing

For the fourth challenge let’s look at parsing Dates from text strings. To view the previous challenge, click HERE.

A dataset contains a text field that has a date embedded within the text. The problem is that the date is represented a few different ways. For example:

16-APR-2005
Nov•16,•1900
4-SEP-00
Jan•5•2000

The goal is to create a new Date/Time field populated with the dates contained within the text field. You will also need to standardize the dates so that they are all formatted the same.

We have listed this as an advanced exercise since parsing out the dates can be challenging depending on the technique you employ to do it. As always, we love to hear your comments. Have fun!

UPDATE 12/7/2015:

The solution has been uploaded

challenge_4_start_file.yxmd

challenge_4_solution.yxmd

Parse

Preparation

Advanced

Data Analysis

Difficult

Accepted answers

All comments

dataMack

Thanks for posting these. I'm using them as learning exercises for the analysts on my team. Its fun to see the different approaches someone may take to solve the same problem- especially when they explain why. We all seem to learn new things this way.

TaraM

We updated this exercise with the solution. Be sure to check out this week's exercise.

dataMack

Thanks Tara. Would it be possible for the analyst the develops the recommended solution to post a brief write up explaining their choices in solving the problem? There's always multiple ways to approach such a problem and I think it's extremely helpful for learning to understand they 'why' behind a solution.

For example, in the first RegEx tool node, 4 output fields are configured, which then get evaluated into a single output via a sequence in a single formula tool. As I examine the RegEx tool, I wonder why 4 outputs were needed? Does each solve for a particular format? Are they designed to be mutually exclusive, etc.

I think of there was just a brief 1-2 sentence explanation for the particular choices, it would greatly enhance understanding of the solution.

GeneR

Hi @dataMack,

Like you said, there is always multiple ways to approach work flows in Alteryx. In the case above there was 4 different formats the dates could be in. Since I'm not an expert with RegEx wanted to keep each in its own column for reformatting downstream (thus the 4). If I was a RegEx master I'm sure there is a single statement to identify all the dates and extract them in a nice clean date-time format.

I will try to provide a bit more explanation on approaches in the future, but keep in mind that there are many ways to solve an exercise and we are only posting an example. There is always another way and more often than not, a better way.

Best Regards,

@GeneR

dataMack

Thanks @GeneR That's perfect- I was wondering if you specifically made 4 to catch the possible variations only in the source file, or if the statements were designed to work on other variations that may not be present in that source, but possible in another, similar file. Very much appreciate the explanation- thank you!

mix_pix

This is the first exercise I've downloaded and looked at so far (we just got our Alteryx licenses). I initially looked at the RegEx syntax and thought "my Lord, how am I ever going to unpack that so that I could ever hope to build one of those myself", and then I noticed the drop-down menu next to the Regular Expression text box. That is seriously wonderful...the fact that you can build a RegEx statement by just selecting the components you want in the order you want. I realize this won't be necessary once I familiarize myself with the syntax more, but having this tool for the novice user is gold. Alteryx is an amazing tool. That's all I wanted to say. :-)

-Mike

markp201

My perpective was there were variations for only two formats.

(\d{1,2}(\s|-)\D{3,}(\s|-)\d{2,4})

(\D{3}(\s)\d{1,2}.{1,2}\d{2,4})

mceleavey

My regex skills are lacking, but here goes:

Spoiler

I first created a RecordID column, then used regex to parse by every word. I then transposed to create a column with all values and removed null rows:

Initial split and transpose.PNG

I then took two streams, the first was to parse the month. To do this, I used regex to match the three character month code, and filtered out those where it did not exist:

Parse out month strings to record ID.PNG

Parse out month strings to record ID.PNG

The second stream was to isolate the day and year figures, which were numeric. I used the data cleansing tool to remove non-numeric characters and removed null rows. I then dropped all fields but the values and the recordID, I then created a title for the crosstab, and pivoted the data:

Parse out day and Year values.PNG

I then simply joined the two streams together on the RecordID field, used a formula to add the date elements together into a date string, then formatted the date:

Join together and format dates.PNG

challenge_4_solution.yxmd

KatieA

Solution attached!

KA_challenge_4.yxmd

MarqueeCrew

I thought that I was pretty clever

mark

challenge_4_MarqueeCrew.yxmd

SeanAdams

done - but clearly did this the long way round

Spoiler

- split out each different format of date using Reg-Ex; and a custom date converter
- commented each section with the date format for easy maintenance

Thank you to @markp201 for the regex code - good to see the patterns like this - and to @MarqueeCrew for the super-scary-looking-inline-formula version (very impressive!)

challenge_4_SeanSolution.yxmd

MarqueeCrew

@SeanAdams,

i would be be happy to walk you through my scary code. It really is just a big OR statement to search for the different patterns.

Let me me know if you're interested.

NicoleJohnson

Solution posted.

Spoiler

challenge_4_NicoleJohnson.yxmd

Laurap1228

This one was tough! I definitely used the On Demand Regex video to help me solve it.

Spoiler

challenge_4_LP.yxmd

estherb47

challenge_4_EHB_solution.yxmd

DE0413

Spoiler

I used a Regex Parse to Parse the 3 date formats into 3 new fields. Next, I used a formula tool to create a field with a consistent format of the dates, finally I used a select tool to deselect extra fields and rename the final date formatted field.

I don't know why my attachments are opening in html and not Alteryx.

AlteryxWeeklyChallengeWeek4.yxmd

LordNeilLord

The datetimeparse tool makes life so much easier...

Spoiler

challenge_4_LNL.yxmd

irisTZ

this one is hard...

challenge_4_with Solution.yxmd

KOBoyle

Solution attached. I wasn't familiar with RegEx, so I needed to do some research and peak at the solution. I found using a single formula after the RegEx parse to be simpler.

Ken

challenge_4_solution_KO.yxmd

Philip

Weekly challenge 4 done.

Spoiler

Formula tool:
IF REGEX_Match([Field_11], "\d+-\u{3}-\d{2}")
THEN DateTimeParse([Field_11], "%d-%b-%y")
ELSEIF REGEX_Match([Field_11], "\d+-\u{3}-\d{4}")
THEN DateTimeParse([Field_11], "%d-%b-%Y")
ELSEIF REGEX_Match([Field_11], "\d+-[\u\l]+-\d{4}")
THEN DateTimeParse([Field_11], "%d-%B-%Y")
ELSEIF REGEX_Match([Field_11], "[\u\l]{3} \d+, \d{4}")
THEN DateTimeParse([Field_11], "%b %d, %Y")
ELSEIF REGEX_Match([Field_11], "[\u\l]+ \d+ \d{4}")
THEN DateTimeParse([Field_11], "%B %d %Y")
ELSE NULL()
ENDIF

JORGE4900

I really enjoyed working on this solution because I appreciate the advantages of regular expressions. On my first try, I did a long version of columns using additional regex tools; on my second try, I used a slightly different approach with a single regex tool. Somehow on this last try, I ended up parsing a T on column 6.

I guess that I should have used the two or more digits option instead of forcing the two and four digit year onto their own columns.

challenge_4_Jorge_Solution.yxmd

JORGE4900

That was a pretty good solution @MarqueeCrew

nick_ceneviva

Awesome challenge to use the RegEx tool. Solution is attached.

challenge_4_Ceneviva.yxmd

Amarendra

This link from @AlexKo helped me understand the 'RegEx' Thank you Alex for such a comprehensive resource.
Attached is the 4th challenge solution.

Amar_challenge_4_start_file.yxmd

dheerajpoojari

My solution attached. was a good refresher to regex

Spoiler

challenge_4_start_file_Dheer.yxmd

derekbelyea

This was a good exercise to reinforce my regex skills. My solution looks similar to others I found here.

Derek

challenge_4_db_solution.yxmd

jamielaird

Here's my solution.

Spoiler

1) I started off by adding a record ID, isolating the part of the string containing the date and removing redundant characters:

Screen Shot 2017-08-20 at 16.28.29.png

2) I then used a formula tool to create three streams corresponding to the different date formats, first using Regex to test if the first character was a digit, which enabled me to identify the [Mon dd yyyy] strings, and for those where this was not the case using FindString to check if the last four characters contained a hype, which enabled me to identify [dd-Mon-yyyy] and [dd-Mon-yy]. These streams then fed into an appropriately configured DateTime tool.

Screen Shot 2017-08-20 at 16.33.22.png

3) Finally a bit of column cleaning and joined back to the original dataset using the record ID.

Screen Shot 2017-08-20 at 16.39.21.png

challenge_4_solution_jamie-laird.yxmd

KayT

challenge_4_KT.yxmd

A_Twa

This one was a real challenge for me. I did get the same results as the output indicated, but I have real doubts that the WC FIelds quote is from 2000....

challenge_4_start_file_AJT.yxmd

Lorna_Eden

challenge_4_LE.yxmd

Natasha

Just starting with Regex and here is my solution

Spoiler

challenge_4_NK.yxmd

JacobR77

Took the long way round (first time using RegEx), but got there.

Challenge 4- Date Parsing.yxmd

BenMoss

As others have said this is a great exercise for working on improving your regex skills.

Here's my solution.

Spoiler

Ben

challenge_4_start_file - BM.yxmd

kcoulon

Challenge Complete!

challenge_4_solution.yxmd

JoeS

For only having one tool in my solution I am glad you can't see my run count while I sorted the regex out.

Went by identify each type of date format and adding it to the regex as and or (the pipe |) and then did the same number of steps to parse it, although had to do it as an elseif statement rather than the cleaner regex.

Spoiler

challenge_4_solution_JS.yxmd

Paul-Evans

@MarqueeCrew

Thanks for your solution. I now know how to refer to marked groups in Regex formulas (e.g. $1) and went down a nice rabbit hole learning about lazy and greedy quantifiers to make sense of how the ".*?" behaved (which is much simpler than the workaround I came up with).

asteryx

Another good lesson - from trying the challenge and reading through the other solutions (@markp201 and@MarqueeCrew showing off some RegEx skills).

I should have thought of a simple formula in a new field to concatenate the RegEx results, but did a Transpose/Summarize instead.

Spoiler

challenge_4_solution_Asteryx.yxmd

JORGE4900

Hello there @dataMack

Here my second take on this solution. I submitted my first one in 2017.

Without taking a peak at the other's solutions, I knew that it could be done to use one single REGEX tool to extract the 6 different date formats in this weekly challenge. Once all those date formats have been extracted, the approach is to create a null date field and start date parsing the matched formats; thus, you can see that if a date parse has been executed by matching it, the following attempts are not executed because the date field would not be null anymore.

The approach to extracting the 6 different date formats has to be from longest to shortest; we would first need to identify and define a DD-MM-YYYY before a D-MM-YY in the REGEX tool; the formula date parsing would work on a similar fashion; it would first parse the longest matches before parsing the shortest matches.

Spoiler

challenge_4_Jorge_Solution_Take_Two.yxmd

MikeLR

Brilliant tip @mix_pix , I hadn't noticed that you can see an explanation of the different regex characters. Thanks!

DotDigitals

Good solution, but I think this is a long solution, there are shorter ways to achieve this.

Check this out

Data Parsing.yxmd

hema_sampath

Spent some time and finally solved!

challenge_4_start_file.yxmd

DemandEngineer

Hi I was thinking the same as you but I found Designer acting strangely... did you find the same? Is it a bug? or am I misunderstanding how RegEx works... please help me understand.

This is what I ended up using:

.*(\d{2}-[[:alpha:]]+-\d{2,4}).*|.*(\d-[[:alpha:]]+-\d{2,4}).*|.*([[:alpha:]]{3}\s\d{2},\s\d{4}).*|.*([[:alpha:]]{3}\s\d{1,2}\s\d{4}).*

Because it didn't parse properly when I used this at the beginning

.*(\d{1,2}-[[:alpha:]]+-\d{2,4}).*

Also didn't know how to handle the variation where there was a comma and space after the day of month and no comma just a space... So I just repeated most of the pattern and made another variation.

I would lose the first character of the date each time... is it trying to minimize what is matched? If yes, is there a better way to write this without so much overlap?