topic Re: Challenge #4: Date Parsing in Weekly Challenges

Challenge #4: Date Parsing

GeneR — Mon, 24 Jul 2023 13:01:24 GMT

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

Re: Alteryx Weekly Exercise - Week #4 Date Parsing

dataMack — Tue, 01 Dec 2015 03:34:03 GMT

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.

Re: Alteryx Weekly Exercise - Week #4 Date Parsing

TaraM — Mon, 07 Dec 2015 21:27:08 GMT

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

Re: Alteryx Weekly Exercise - Week #4 Date Parsing

dataMack — Mon, 07 Dec 2015 22:03:10 GMT

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.

Re: Alteryx Weekly Exercise - Week #4 Date Parsing

GeneR — Mon, 07 Dec 2015 22:50:29 GMT

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

Re: Alteryx Weekly Exercise - Week #4 Date Parsing

dataMack — Mon, 07 Dec 2015 23:07:11 GMT

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!

Re: Alteryx Weekly Exercise - Week #4 Date Parsing

mix_pix — Mon, 14 Dec 2015 19:13:10 GMT

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

Re: Alteryx Weekly Exercise - Week #4 Date Parsing

markp201 — Mon, 06 Jun 2016 16:36:34 GMT

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})

Re: Challenge #4: Date Parsing

mceleavey — Mon, 23 Jan 2017 18:02:48 GMT

My regex skills are lacking, but here goes:

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:

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:

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:

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:

Re: Challenge #4: Date Parsing

KatieA — Sun, 26 Feb 2017 21:02:34 GMT

Solution attached!

Re: Challenge #4: Date Parsing

MarqueeCrew — Fri, 03 Mar 2017 18:03:26 GMT

I thought that I was pretty clever :D

mark

Re: Challenge #4: Date Parsing

SeanAdams — Sat, 04 Mar 2017 19:57:56 GMT

done - but clearly did this the long way round

- 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!)

Re: Challenge #4: Date Parsing

MarqueeCrew — Sat, 04 Mar 2017 20:03:34 GMT

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

Re: Challenge #4: Date Parsing

SeanAdams — Sun, 05 Mar 2017 17:39:27 GMT

Thank you @MarqueeCrew - walking through it, the OR logic makes sense - the part that I wouldn't have come to on my own steam is the way that it seems that you've replaced the outer part of the string, rather than the pattern-matched date (it seems kinda like saying "this is how you recognize an apple seed - now throw away the rest") :-). it's not an intuitive pattern, but makes sense once you see it.

It would be good to make contact, even if just to extend the network of like-minded folk - not sure how to PM you on the Alteryx infra, but I've sent you a ping on LinkedIn and Google+

Many thanks for your kind offer @MarqueeCrew

Sean

Re: Challenge #4: Date Parsing

NicoleJohnson — Fri, 07 Apr 2017 04:43:18 GMT

Solution posted. :)

Re: Challenge #4: Date Parsing

Laurap1228 — Sun, 16 Apr 2017 10:34:54 GMT

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

Re: Challenge #4: Date Parsing

estherb47 — Wed, 26 Apr 2017 19:13:15 GMT

Re: Challenge #4: Date Parsing

DE0413 — Thu, 04 May 2017 15:42:27 GMT

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.

Re: Challenge #4: Date Parsing

LordNeilLord — Sat, 20 May 2017 17:38:37 GMT

The datetimeparse tool makes life so much easier...

Re: Challenge #4: Date Parsing

irisTZ — Fri, 09 Jun 2017 14:34:36 GMT

this one is hard...