Backfilling a column with an expected value

[jdallen75]

Hello, 

The ultimate dataset in my macro has 2 header rows which are prepended using a Text Input tool, at which point the Filter Province is Null:

The subsequent rows are generated in the macro using a control parameter and filter loop, so each iteration through the loop, rows 3 onwards will all have the same Filter Province.

My goal is to backfill the Filter Province of the first 2 rows to match. I tried using a multi-row formula with [Filter Province + 1], but that only backfills row 2. I was thinking a formula, where if Filter Province is null, use an aggregate like "Last" or "Max", but can't identify the right operand.

Any suggestions are appreciated.

[chandler-gjino · Accepted solution]

you could try the same logic but instead of [Row+1:Filter Province], do 

if isnull([Filter Province])
then [Row+2:Filter Province]
else [Filter Province] endif

just be sure to increase the 'Num Rows' to 2 in the upper configuration

[jdallen75]

Excellent - I forgot the fact that one can increase the number of rows forward/backward. Thanks!