Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

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

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #21: Date Reformatting

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

TaraM
Alteryx Alumni (Retired)

A solution has been posted

Spoiler
2016-04-18 08_32_17-Alteryx Designer x64 - Date_Reformatting_Beginner_Solution.yxmd.png
Tara McCoy
MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
SeanAdams
17 - Castor
17 - Castor

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

NicoleJohnson
ACE Emeritus
ACE Emeritus

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
SeanAdams
17 - Castor
17 - Castor

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

rijuthav
6 - Meteoroid

My Solution.

 

Used the Delimiter to get the month.

 

 

estherb47
15 - Aurora
15 - Aurora

Another fun solution

 

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

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