Challenge #21: Date Reformatting

[GeneR]

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]

A solution has been posted

Spoiler

[MarqueeCrew]

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

[SeanAdams]

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]

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

[SeanAdams]

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]

My Solution.

Used the Delimiter to get the month.

[estherb47]

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]

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]

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

Spoiler