topic Re: Challenge #58: An Odd String to Date Conversion in Weekly Challenges

Challenge #58: An Odd String to Date Conversion

JoeM — Tue, 09 May 2023 17:46:37 GMT

The solution to last week's challenge is HERE.

This week we will be offering our third ever challenge on converting strings to dates. Sometimes these may be simple, but every once in awhile, we come across those source systems that drop things in a format that are better understood by machine than by a human. In this week's challenge, convert the string to a date using the following rules:

1) The Input contains dates formatted as year, month, day where the first character details if the year begins with 19 or 20.

2) It is 19 when the first character is 0 and 20 when the character is 1.

3) The remainder of the date following the 0 or 1 is the remaining year digits followed by month followed by day.

Example: 1040202 should become 2004-02-02.

Re: Challenge 58: An Odd String to Date Conversion

lbansal — Mon, 20 Feb 2017 08:10:17 GMT

Here's my solution. Quite an easy one.

Thanks.

Re: Challenge 58: An Odd String to Date Conversion

Max06270 — Mon, 20 Feb 2017 17:47:16 GMT

Hey guys,

It is actually pretty simple but I was stuck for 15 min with...

the fact that my result was truncated to 7 characters all the time... then I realize that I had to change the field type or size...

Thanks and have a great week all,

Maxime

Re: Challenge 58: An Odd String to Date Conversion

JeffF — Mon, 20 Feb 2017 22:01:13 GMT

Single formula method.

Re: Challenge #58: An Odd String to Date Conversion

paul_houghton — Tue, 21 Feb 2017 12:15:54 GMT

Nice little thinking problem. posted mine on the gallery

My example

Re: Challenge #58: An Odd String to Date Conversion

PaulRB — Tue, 21 Feb 2017 13:40:31 GMT

Too easy.

Re: Challenge #58: An Odd String to Date Conversion

Hartwick96 — Tue, 21 Feb 2017 23:34:17 GMT

Here is my attempt...

Re: Challenge #58: An Odd String to Date Conversion

rajiv_tarafdar — Wed, 22 Feb 2017 05:36:23 GMT

As this could be used for multiple conversions, I made a macro out of this.

Cheers

Rajiv

Re: Challenge #58: An Odd String to Date Conversion

DinoF — Wed, 22 Feb 2017 09:59:20 GMT

Hi,

I would love to participate to these challenges (to improve my knowledge and learn stuff), however i only have an acces to a 9.5 version of Alteryx so when i try to participate i can't open the input :(

Do you think that it would be possible to have it compatible with my granpa version ?

Cheers

Re: Challenge #58: An Odd String to Date Conversion

JohnG — Wed, 22 Feb 2017 14:53:49 GMT

My solution. Looking at other examples and I ended up with a very similar approach. Why break a working system :)

Re: Challenge #58: An Odd String to Date Conversion

JoeM — Wed, 22 Feb 2017 15:26:30 GMT

@DinoF, I will try to be more diligent with making more backward compatible workflows. Attached, I have a workflow that should work with your version. If you ever find yourself in a spot where you do not have a compatible workflow version, you can make is compatible (most of the time) by following this knowledgebase article.

Also, check out the "modify Alteryx workflow version" that @MarqueeCrew has posted on the Gallery!

Re: Challenge #58: An Odd String to Date Conversion

DanielUpton — Wed, 22 Feb 2017 22:53:59 GMT

Here's a solution on v11. My first chance to play with the updated formula tool. I like!

Re: Challenge #58: An Odd String to Date Conversion

DinoF — Thu, 23 Feb 2017 14:21:19 GMT

Hi there, thanks for the tip (modifying the XML with notepad) !

Cheers

Re: Challenge #58: An Odd String to Date Conversion

DinoF — Thu, 23 Feb 2017 14:45:33 GMT

Well this was a good exercise (more like a good break), you'll find my solution enclosed :) BTW the limitation on the lentgh to 7 char was a good one i'll give you that !

Re: Challenge #58: An Odd String to Date Conversion

MarqueeCrew — Thu, 02 Mar 2017 13:32:29 GMT

@JoeM,

Used a single formula for this one.

datetimeparse(tostring(19+tonumber(left([date],1)))+substring([date],1,2)+right([date],4),"%Y%m%d")

Cheers,

Mark

Re: Challenge #58: An Odd String to Date Conversion

JoeM — Thu, 02 Mar 2017 15:27:48 GMT

@MarqueeCrew

Nicely done! Now I have to play too! I probably would have done something near identical to what you did, but decided to try to be creative and find another way.

tostring(tonumber(left([date],1))+19)+REGEX_Replace([date],".(.{2})(.{2})(.{2})","$1-$2-$3")

Re: Challenge #58: An Odd String to Date Conversion

JoshK_dup_167 — Fri, 03 Mar 2017 01:09:13 GMT

Regex + Formula tool

Re: Challenge #58: An Odd String to Date Conversion

mceleavey — Thu, 30 Mar 2017 15:30:03 GMT

Ok, I may have taken a slightly different approach:

I first identified and isolated the first character of the string, which denotes the year, using the simple left([date],1) formula.
I then created a text input as a mapping table:

I then joined this table on the first character, then took the new year value.
I then simply appended the year to the rest of the data string, effectively replacing the first character with the appropriate year value. Then I simply parsed the date.

Re: Challenge #58: An Odd String to Date Conversion

SeanAdams — Wed, 05 Apr 2017 18:33:00 GMT

simple 3 box solution

formula to split
Date time convert to convert
Select to clean up

Re: Challenge #58: An Odd String to Date Conversion

NicoleJohnson — Fri, 07 Apr 2017 21:22:02 GMT

My solution.