Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Formula Error: Filling down is leaving Null values

agriese
7 - 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

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

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
17 - Castor
17 - Castor

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
7 - 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.

Thableaus
17 - Castor
17 - Castor

@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
17 - Castor
17 - Castor

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