community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Formula Error: Filling down is leaving Null values

Meteor

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])

counting null error.PNG

Alteryx Certified Partner
Alteryx Certified Partner

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,

Alteryx Certified Partner
Alteryx Certified Partner

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,

Meteor

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.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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,

 

Alteryx Certified Partner
Alteryx Certified Partner

@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, 

Meteor

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!

Labels