Alteryx Weekly Challenge #21: Multi-Row Formula

by Valerija Kirjackaja

Multi-row Formula is an extremely powerful and useful tool in Alteryx. The Alteryx weekly challenge #21 is a great and simple example of how it works.

The aim of the challenge is to fix the Date input field by separating the month and year into two separate columns and adjusting in such a way that each month is written in a three-letter format and the year column is filled (no empty cells).

Input

There are various ways to split the month and year into separate columns but I decided to use the RegEx tool I learned about in out training session two days ago. I applied the RegEx parse method to the Date field using the following expression: (\u)(\d{2})?. This stands for “Get me a column which contains one capital letter, and another column which contains two digits, if they exist in the original field ”. I renamed these columns as ‘Month’ and ‘Year’ and left both as strings.

The outcome of this step should look like this:

In the next step, we need to fill in the blanks in the Year column, which is where the Multi-Row Formula tool will come in handy. Essentially, what we want it to do is “if the cell in the Year column is empty, get the value from the cell above it, otherwise, keep it as it is”. Hence, we can use the multi-row tool to update the existing Year Field and its configuration should look like this:

IF ISEMPTY([Year]) THEN [Row-1:Year] ELSE[Year] ENDIF

This will fill up the Year column (no need to group on anything).

The final step now is to fix the month names to contain the first three letters instead of just the first one (e.g. Jan, Feb, etc). The single capital letter format is not recognised by Alteryx, so we cannot use the DateTime parsing tool. We also cannot use a simple formula tool with conditional statements for each letter (e.g. IF [MONTH] == ‘J’ THEN ‘Jan’, […]) because the first letters are not unique (i.e. ‘J’ stands for Jan, Jun and Jul). So, to solve this, we will need to apply another multi-row formula tool. We will update the existing Month field using the following expression:

IF [Month] == ‘J’ AND [Row+1:Month] == ‘F’ THEN ‘Jan’
ELSEIF [Month] == ‘F’ THEN ‘Feb’
ELSEIF [Month] == ‘M’ AND [Row+1:Month] == ‘A’ THEN ‘Mar’
ELSEIF [Month] == ‘A’ AND [Row+1:Month] == ‘M’ THEN ‘Apr’
ELSEIF [Month] == ‘M’ AND [Row+1:Month] == ‘J’ THEN ‘May’
ELSEIF [Month] == ‘J’ AND [Row+1:Month] == ‘J’ THEN ‘Jun’
ELSEIF [Month] == ‘J’ AND [Row+1:Month] == ‘A’ THEN ‘Jul’
ELSEIF [Month] == ‘A’ AND [Row+1:Month] == ‘S’ THEN ‘Aug’
ELSEIF [Month] == ‘S’ THEN ‘Sep’
ELSEIF [Month] == ‘O’ THEN ‘Oct’
ELSEIF [Month] == ‘N’ THEN ‘Nov’
ELSE ‘Dec’
ENDIF

This type of formula allows us avoid ambiguity by checking what month come after (or before) the current one.

Full workflow
Output
Avatar

Valerija Kirjackaja