For this blog post I will do a guided, step-by-step walkthrough of a solution to Alteryx weekly challenge #193, with a specific focus on the ‘MULTI-FIELD FORMULA’ tool.

This data set contains a list of unofficial holidays throughout the year and the date these holidays take place. The aim here is clean the list of holidays and ensure they are presented in a chronological order.

The first issue with this data set is that every field contains trailing spaces. One possible idea to remove these trailing spaces would be to use the ‘data cleansing’ tool, but in this particular instance, I will use the MULTI-FIELD FORMULA tool. If we select ‘text’ fields on this tool and click on both columns, we can add a simple “trim([_CurrentField_])” expression, which will remove all trailing spaces in every field in one fell swoop.

We may have cleaned the data here, but our ‘days’ column comprising of each date is not in chronological order. To amend this, we can use the DATE TIME tool to create a new column with days formatted in the yyyy-MM-dd format that Alteryx requires. This will initially create dates which are wrong, but this is simply a dummy column for now.

Now we will use the SORT tool to sort the newly converted date column into ascending order. This means our original column with the dates of each unofficial holiday is now in the corrected, chronological order. All that is left to do now is to remove the newly formatted, dummy date column using the SELECT tool, giving us a full list of unofficial holidays in a correctly ordered manner.