Multi-row formulas allow users to access rows above or below the current row, giving us the ability to craft powerful formulas such as moving averages, or filling in missing rows using previous or future row values. This blog will look at solving Alteryx weekly challenge #21, which asks us to reformat poor rows into separate years and months columns.

The workflow for this involved only 4 steps, and lengthy ‘if-then’ conditions for the final mult-row formula.

To solve this, I took an approach that firstly extracted the year from each row, leaving it null if there was no year shown (leaving me with 07 and 08 in rows 1 and 13 respectively). Then, I extracted the month which could be done by taking the first left hand value from the Date column. This leaves us with two columns, with the next job to use a multi-row fomula to fill all null values with the desired year.

This multi-row formula was simple enough, and the logic could be defined as:

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

What this formula does is look at the current row, and if the field is blank, it uses the value from the previous field to replace the null value. This leaves us with the first 12 rows having the year ’07’, with the final 12 rows ’08’.

The final multi-row formula was a lengthy process, since there was quite a lot of logic required to ensure that the correct months were parsed in. For example, we cannot simply say that ‘J’ is ‘January’, since that would incorrectly fill out ‘June’ and ‘July’. The logic can be seen below:

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’ 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’
ELSEIF [Month]==’D’ THEN ‘Dec’
ELSE ”
ENDIF

This statement was frustrating, but effective at correctly filling out the month column. I found that working out the condition for January to be the hardest, since we cannot simply say that it is January if the previous month is null, since that would not work for filling in January for the 2008 date. My workaround for this was to check whether the next month was ‘F’, providing a useful workaround for this problem.

Finally, I used a select tool to only keep required columns, and that gave me the required output. I find multi-row formulas to be intuitive, but it is really important to define the logic beforehand!