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 #21: Date Reformatting

Alteryx Alumni (Retired)

The link to last week’s challenge (challenge #20) is HERE.  For this week’s challenge we have an easier one that requires the reformatting date fields.  The input data has very poorly formatted month and year information that will require you to build some logic to make it usable. 

 

Use Case: A company needs to reformat a month/year flag in a data asset they received for a consulting project.

You will need to take the Date field provided and separate it into fields for the Month and Year.  For example, F07 should become 2 columns where month is Feb and year is 07.

 

Have fun!

Creative Director
Creative Director

A solution has been posted

Spoiler
2016-04-18 08_32_17-Alteryx Designer x64 - Date_Reformatting_Beginner_Solution.yxmd.png
Tara McCoy
Alteryx Certified Partner
Alteryx Certified Partner

spoiler alert:  switch + IF

Spoiler
IF [Month] in ('F','S','O','N','D') THEN SWITCH([Month],'xxx','F','Feb','S','Sep','O','Oct','N','Nov','D','Dec')
ELSEIF
[Month] == 'J' && [Row+1:Month] == 'F' THEN 'Jan' ELSEIF
[Month] == 'J' && [Row+1:Month] == 'J' THEN 'Jun' ELSEIF
[Month] == 'J' THEN 'Jul' ELSEIF
[Month] == 'M' && [Row+1:Month] == 'A' THEN 'Mar' ELSEIF
[Month] == 'M' THEN 'May' ELSEIF
[Month] == 'A' && [Row+1:Month] == 'M' THEN 'Apr' ELSEIF
[Month] == 'A' THEN 'Aug'
ELSE 'XXX'
ENDIF

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Aurora
Aurora

I took a slightly different route which will may be a little quicker on a large data-set

 

Spoiler
Rather than doing string processing on the month field, I used a monthID field, and joined to a static table that has month names.

Because it's an integer join, it's going to be a lot faster on large data-sets than doing string-based cascaded logic - however it won't work well if the data arrives out-of-order because the month ID logic will fail.

Other than that - the solution is very similar to others provided

Solution! Ended up having to take the same track as @SeanAdams with the Text Input + Join for month names, but I also like @MarqueeCrew solution with the Switch formula, will have to log that away as well.

 

Another potential tool improvement request (unless, again, I am missing something that already does this): Would love to see enhancements to DateTimeFormat that allow you to indicate Month and day of week in the same manner Excel does when you use the Text formula, as words/partial words instead of numbers... For example, "MM" = 02, "MMM" = Feb, "MMMM" = February... or "dd" = 07, "ddd" = Wed, "dddd" = Wednesday. Anyone else feel this would be useful? Certainly would have streamlined this challenge, but I can see it having useful application in a variety of situations where standard DateTime formats as values are just not sufficient.

 

Spoiler
WeeklyChallenge21.JPG
Aurora
Aurora

Agree with you about the need to relook some of the data parsing & conversion functionality @NicoleJohnson - there's a few ideas in the Ideas section that you could add to.

 

@MarqueeCrew suggested something similar to you here: https://community.alteryx.com/t5/Alteryx-Product-Ideas/Alteryx-making-dating-easier/idi-p/20625

I suggested refactoring data-time treatment to make it the same throughout - in some places month is "%b" (like DateTimeParse), in some its "Months" (like DatePart) and in some it's "Mon" (like the date time tool).   I think this is an indication that these were built at different times as thinking was evolving, and it's probably time to refactor the treatment of DateTimes thoroughly and make this consistent and simple.

 

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Refactor-Date-time-treatment/idc-p/51767#M357...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Add-interval-Types-to-the-DATETIMEDIFF-functi...

 

Have a look - and if you agree with any of these two ideas (or the raft of other dateTime related ideas in the Ideas section) - give them a star and create some buzz around them by talking about them to others so that we can get them up the priority queue for future release.

 

Thanks @NicoleJohnson

Sean

Meteoroid

My Solution.

 

Used the Delimiter to get the month.

 

 

Quasar

Another fun solution

 

Spoiler
Spoiler - Switch can be useful in this case, especially if you create a row identifier for the months (1-12)
Alteryx Certified Partner
Spoiler
I  solved this with two Multi-Row Tools.
The first created the Month Field using this formula:
IF Length([Date])>1 THEN "Jan"
ELSEIF [Date]="F" THEN "Feb"
ELSEIF [Date]="M" AND [Row-1:Date]="F" THEN "Mar"
ELSEIF [Date]="A" AND [Row-1:Date]="M" THEN "Apr"
ELSEIF [Date]="M" THEN "May"
ELSEIF [Date]="J" AND [Row-1:Date]="M" THEN "Jun"
ELSEIF [Date]="J" THEN "Jul"
ELSEIF [Date]="A" THEN "Aug"
ELSEIF [Date]="S" THEN "Sep"
ELSEIF [Date]="O" THEN "Oct"
ELSEIF [Date]="N" THEN "Nov"
ELSE "Dec" ENDIF
The second created the Year Field using this formula:
IF Length([Date])>1 THEN Right([Date], 2) ELSE [Row-1:Year] ENDIF
Alteryx Certified Partner

I didn't fancy writing out a lengthy formula to calculate the months so I took a slightly different route...

Spoiler
Weekly Challenge 21.png