Replacing NULL values with previous year's values

[gillburling]

I am trying to figure out how to replace values when an input on each line says to use another row's values.

So if the data looks like this:

ID	survey_year	prev_value_ind	value
001	2000	n	500
001	2001	y	[null]
002	2000	n	550
002	2001	n	570

I would like it to look like this:

ID	survey_year	prev_value_ind	value	value_final
001	2000	n	500	500
001	2001	y	[null]	500
002	2000	n	550	550
002	2001	n	570	570

I should also note that due to it being survey data, there are MANY "value" columns that would need to be replaced (if prev_value_ind is 'y', then they would all be null and all need to be replaced), so I might be better served by replacing the value column with the value_final column instead of creating new. If I'm creating copied "final" columns, I would end up with about 92 columns instead of 46.

[fmvizcaino · Accepted solution]

Hi @gillburling ,

A Multi-row formula will do the trick.

Example attached.

Best,

Fernando Vizcaino

[gillburling]

Thanks for the speedy reply, @fmvizcaino

It looks like I got it all to work after making sure the data set was appropriately sorted!

I want to make sure I'm applying this to this and other data sets appropriately. Could you point me to a resource or explain how the expression works? Particularly the [Row-1:value]?

[fmvizcaino]

Hi @gillburling ,

The multi-row tool works always related to the current cell, it is pretty much the same as creating an excel formula refering to the row above and drag the formula down.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Multi-Row-Formula/ta-p...

https://www.youtube.com/watch?v=xDYLxsQ8ibc

Best,

Fernando Vizcaino

[gillburling]

What about if there are multiple years missing? Is there some way to nest this expression?

id	survey_year	prev_value_ind	value
001	2000	n	500
001	2001	y	null
001	2002	y	null
001	2003	y	null

should be:

id	survey_year	prev_value_ind	value
001	2000	n	500
001	2001	y	500
001	2002	y	500
001	2003	y	500

[fmvizcaino]

Hi @gillburling ,

The multi-row checks row by row and fill them sequentially, so if the second row is null, it will replace the null with the value before and when checking the third row, the second row was replaced already, so it works as you need.

Example attached.

Best,

Fernando Vizcaino