Formula Error: Filling down is leaving Null values

[agriese]

Hi all,

I'm having a little trouble with the Multi-Row Formula tool. I'd like to add a count in a pre-existing column. If the the current row has a count, it keeps that count, but if the current row is null, it takes the previous row's count. I've been using this function, but it's leaving me with some null values. Any advice?

iif(isnull([Duplicate Key]),[Row-1:Duplicate Key],[Duplicate Key])

[Thableaus]

Hi @agriese 

Are you using Group By clause in your Multi-Row formula Tool?

If you are, keep an eye on it. It could be causing this problem.

Cheers,

[Thableaus]

Some other thing you need to be aware of:

Field types.

It'd be good if you shared your configuration part of Multi-Row formula tool.

Cheers,

[agriese]

Hi Thableaus, here's the current configuration. I didn't think I was grouping by anything, and all my numeric fields are set to Int16.

[Thableaus]

@agriese 

Aren't you planning to modify your same field used in formula (Duplicate Key)?

If so, you need to select option Update Existing Field.

Please also share a print screen of your results tab with output from all fields.

Cheers,

[Thableaus · Accepted solution]

@agriese 

Ok, now I might have identified the problem.

Maybe when there are 2 Nulls in a Row, it does not change value.

This is because you're creating a new column (Duplicate ID) and referencing Duplicate Key.

So if there's something like this:

1

Null

Null

New column output would be:

1

1

Null

That's because it's saying - IF Duplicate Key is null, then get the row before of duplicate key (it does not matter if it is null or not).

On the other hand, if you update existing column Duplicate Key, this would not happen, because as soon as Null value is updated, previous row will never be Null (even if there is a sequence of Nulls).

Cheers, 

[agriese]

Hi @Thableaus , that was the issue. When I switched to Update Existing Field and set that field to Duplicate Key, it ran as expected. Thanks much!