I have a dataset where I'd like to increment the value by 1 in the column "Box" based on the previous value when it is not null. I've tried using a Multi-Row Formula tool with the parameters listed below, but the result is always still a null value.
Update Existing Field: Box
Num Rows: 1; Set Values to Closest Valid Row (also tried NULL and 0 or Empty)
No Grouping
Expression: if isnull([Box]) then [Row-1:Box]+1 else [Box] endif
I've also checked that the Box column is a Double type, not sure what else I'm missing! Thanks in advance for any ideas you might have!
Current Data Set
Box | Date | Type | Price | Shares |
925 | 2021-12-01 | RSU Tax Shares | 518.98 | 16,992 |
926 | 2021-12-10 | RSU Tax Shares | 513.22 | 9 |
927 | 2021-12-31 | RSU Tax Shares | 538.12 | 783 |
[Null] | 2022-01-03 | RSU Tax Shares | 537.95 | 216 |
[Null] | 2022-01-04 | RSU Tax Shares | 523.85 | 163 |
[Null] | 2022-01-06 | RSU Tax Shares | 523.85 | 1,236 |
Outcome Needed:
Box | Date | Type | Price | Shares |
925 | 2021-12-01 | RSU Tax Shares | 518.98 | 16,992 |
926 | 2021-12-10 | RSU Tax Shares | 513.22 | 9 |
927 | 2021-12-31 | RSU Tax Shares | 538.12 | 783 |
928 | 2022-01-03 | RSU Tax Shares | 537.95 | 216 |
929 | 2022-01-04 | RSU Tax Shares | 523.85 | 163 |
930 | 2022-01-06 | RSU Tax Shares | 523.85 | 1,236 |
Solved! Go to Solution.
Formula looks fine, and it tests fine for me. Can you screenshot your configuration, maybe throw a select tool before the multitool to make sure it's a numeric type?
I clearly need to sleep more! I just realized that I was only looking at the input side of the tool, not the output. Everything is working now and thanks for looking at it!
@linlysergel as @mbarone mentioned your formula is correct. can you check your configuration against the one provided?