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

Hi @gillburling ,

A Multi-row formula will do the trick.

Example attached.

Best,

Fernando Vizcaino

2021-01-06 Replacing NULL values with previous years values.yxmd