community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.

Challenge #46: Formatting a Date from an Integer

Highlighted
Alteryx Alumni (Retired)

The link to last week’s exercise (exercise #45) is HERE

 

I love to share real world use cases as exercises.  This week’s exercise comes from a customer that had data coming from in a legacy system and needed to transform the date field for a new system.  It is a straightforward exercise but shows how easily Alteryx can apply business logic to most any data transformation problem.

 

Use case: The Input contains dates formatted as year, month and day. In this case, the first character determines if the year should begin with 19 or 20.   If the first character is 0 then the year starts with 19, and when the first character is 1 the year starts with 20.  The remainder of the date following the 0 or 1 is the remaining year digits followed by month followed by day.

 

Objective: Please convert these strings into date formatted field.

Quasar
Quasar

How about a formula expression of: DateTimeParse(Switch(Left([date],1),'','0','19','1','20')+Right([date],6),'%Y%m%d')

Alteryx Partner

exc47.PNG

one more approch 

Creative Director
Creative Director

A solution has been posted

Spoiler
2016-10-31 11_06_46-Alteryx Designer x64 BETA - DataPrep_DateFormat_Solution.yxmd_.png
Tara McCoy
Aurora
Aurora

Same approach as provided by @LBhat 

My solution...

 

But someone please tell me I'm not crazy, and that this is exactly the same problem as Challenge #58... right?? I am doing these challenges in a bit of a random order, but I am having a total twilight zone moment because i JUST did this one, I kid you not, earlier today. :)

 

Rather than taking the easy way out, I took at shot at the one-tool formula method (basically just trying to be in the same cool one-tool club as @Joe_Mako from #46 and @MarqueeCrew & @JoeM from #58...). Took a few tries (dates are not my forte), but eventually figured it out. 

 

Spoiler
WeeklyChallenge46.JPG
Alteryx Alumni (Retired)
There was a duplicated assignment.

Gene Rinas
Quasar

Had fun with this one. Broke it into 2 steps in the formula tool to have shorter formulas to audit. Similar/same solution as others

Alteryx Certified Partner

Nice & simple

 

Spoiler
Weekly Challenge 46.png
Alteryx Certified Partner

Working through the backlog of these as I get the chance.

Is it wrong to do these challenges in my free time, in place of going outside and interacting with the "real" world?

I need to get out more...

 

Spoiler
I simply wrote a formula to replace the first character of the date string with 19 or 20 according to if it is a 0 or 1.
I then parsed the date and dropped the extra column.

Solution.PNGResults.PNG