Replacing Null Fields with Imputation

Hi, I currently have 27 columns of integer data. Within some of these columns I have null values in some cells. I want to replace these null values with "0". I figured I would use the imputation tool to do this. However, I am getting an error "A record was created with no fields". I set the imputation properties to 1) Incoming values to replace: Null() and 2) Replace with value: User specified value 0.000. I get this value regardless of which column I select and which user specified value I set it to. Am I misunderstanding this tool?

The only workaround I've thought of is to create a formula that replaces every column. I have 27 columns though so this could take a while...

Thank you!

Join

Preparation

Accepted answers

MarqueeCrew

Did you consider using the multi field formula tool and select your 27 fields and use the formula:

IIF(ISNULL([current_field]),0,[current_field])

danielkresina

RodL,

I encountered this issue as well, and already worked through it with your tech support team. They identified a bug in the macro that drives this tool. I only encountered this after upgrading to Alteryx 10.0.

Attached is a new version that you can copy to your machine in this location: Alteryx\bin\RuntimeData\Macros (probably at this full path C:\Program Files\Alteryx\bin\RuntimeData\Macros) This will update the Imputation tool that is currently on your system, then you should be good to go.

Imputation_v2.yxzp

All comments

MarqueeCrew

Did you consider using the multi field formula tool and select your 27 fields and use the formula:

IIF(ISNULL([current_field]),0,[current_field])

RodL

mxia,

Even though @MarqueeCrew provided a good workaround (Multi-Field formula tool is a great timesaver!), I wanted to let you know that I was able to replicate your error message and have notified our Product team for review.

Thanks!

mxia

Good tip - multifield fixed it for me. I'm still curious if I'm using imputations incorrectly...

Edit - thanks for filing the bug. Good luck fixing it!

danielkresina

RodL,

Imputation_v2.yxzp

lhornstra

This imputation issue has been the thorn in my side for the past two weeks while trying to run a regression model. I followed everyone's advice but now the imputation macro is changing up the output. Some times I will have "right_fieldname" in my regression configuration before I run my workflow, and then after I run the workflow those right fields disappear and my regression is left with only 2 fields in the configuration selected. So then I'll select the original field names that no longer have right before them, and the regression gives me an error that fieldname is missing values (which was the whole reason I used the imputation macro). I'll delete that macro and insert imputation_v2---create record error. Delete that one, put back the original one, same error. Install from comments above, and the output issues. *sigh*.

Counting down the days until there is a new alteryx update released... until then, adding isnull in my SQL.

RodL

Of course, @MarqueeCrew's suggestion using the Multi-Field formula would also be an alternative. Smiley Happy

ChrisF

Hey @lhornstra any chance you could upload a sample workflow that demonstrates the issue? I'd love to take a look at it.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703