Replacing Null Fields with Imputation

[mxia]

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! 

[MarqueeCrew · Accepted solution]

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 · Accepted solution]

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.

[WayneWooldridge]

I ran into a similar issue, but I found Imputation_v2.yxmc to be the one that didn't work correctly.  This is the version of the tool that is on the tool palette.  For the version that did work correctly, I had to go to C:\Program Files\Alteryx\bin\RuntimeData\Macros and select Imputation.yxmc.  Please note I'm using Alteryx Designer version 10.0.9.5845.

[ChrisF]

Hi Wayne, 

You're correct, the fixed version is currently not in the build just yet, but I've attached it below. Let me know if that one works better for you!

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

[ChrisF]

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