Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Challenge #21: Date Reformatting

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

Highlighted
Alteryx
Alteryx

A solution has been posted

Spoiler
2016-04-18 08_32_17-Alteryx Designer x64 - Date_Reformatting_Beginner_Solution.yxmd.png
Tara McCoy
Highlighted
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.
Highlighted
16 - Nebula
16 - Nebula

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

Highlighted
14 - Magnetar
14 - Magnetar

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
Highlighted
16 - Nebula
16 - Nebula

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

Highlighted
6 - Meteoroid

My Solution.

 

Used the Delimiter to get the month.

 

 

Highlighted
14 - Magnetar
14 - Magnetar

Another fun solution

 

Spoiler
Spoiler - Switch can be useful in this case, especially if you create a row identifier for the months (1-12)
Highlighted
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
Highlighted
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