alteryx Community

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

15 - Aurora

Would definitely like to come back to this one and improve my tool usage - I feel like both my formula and splitting of the data could be improved.

Spoiler

So I first start with a text to columns tool to split the data by a delimiter of the letter J, as this is the only case where there was a number.
This produced an output like so:

Then i wanted to remove all the numbers from the Date Column, so I would just have the letters. The data cleansing tool was great for this.

Then I didn't need the middle column, so I removed that and renamed the year column:

Now I was a little bit stuck, as i knew i'd need to run some kind of loop/formula on the data to apply the previous years to column, to fill out the nulls. I did have to look up how to use the previous row, but the logic for applying it was fairly fundamental, if the row is null, apply the last year. If its not null, leave it!

Then originally i applied a nice Switch/Case statement (which i love), but sadly due to the conditions that follow January/June/July (all with the same starting letter) this didn't work!

So sadly, the alternative was a pretty horrid if-elseif statement. I would like to revisit this and implement a lookup table or try to redesign this element of the process:
NOTE: the logic between the different J's. Logic is not required for July for example, as it is the last J in the sequence, and the if statement will only return one value. In the case that it is January, it will not then replace the value with July.

This stage provided me with this data:

In which i just had to remove the date column, and then bring back in the original data column as the output required

6 - Meteoroid

Challenge complete! Did anyone use a tool to convert the month number into Month name besides using a logic formula?

Spoiler

8 - Asteroid

I got it in a different way as posted in the suggested solution but happy not to look at it before completing it!

Spoiler
Spoiler

7 - Meteor

Thanks.

7 - Meteor

Here's my solution.

8 - Asteroid
Spoiler

9 - Comet

This was a fun one but writing big nested logic statements felt a bit forced.

Spoiler
11 - Bolide

Challenge 21 Done.

Spoiler
Workflow

Thanks & Regards,
Allwyn Thomas

8 - Asteroid

I did this in a super convoluted manner. Two multirows to get the Year and a month num. 1-12 for each year. Then a formula tool to get the month string in two digit format. Then a fake_date in order to use the "%b" format method to get the Month Name as Jan Feb etc. Then a select and rinse out the unnecessary fields and rename the remaining to the desired format.

8 - Asteroid

Challenge 21