Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
New content is available in Academy! You may need to clear your browser cache for an optimal viewing experience

Challenge #58: An Odd String to Date Conversion

Highlighted
Director, Customer Enablement
Director, Customer Enablement

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

Highlighted
Alteryx Partner

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

Highlighted
Meteoroid

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

Cheers

Highlighted
Meteoroid

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 !

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@JoeM,

 

Used a single formula for this one.

 

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

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Director, Customer Enablement
Director, Customer Enablement

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

 

Spoiler

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

 

Highlighted
Meteoroid

Regex + Formula tool

Highlighted
Alteryx Certified Partner

Ok, I may have taken a slightly different approach:

 

Spoiler
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:

Text input.PNG

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.
Solution.PNG
Highlighted
Nebula
Nebula

simple 3 box solution

Spoiler
formula to split
Date time convert to convert
Select to clean up
Highlighted
Magnetar
Magnetar

My solution.

 

Spoiler
WeeklyChallenge58.JPG