Imputation tool changes continuous variables to all null values

[danielreedsmith]

Hi Everyone, 

I'm a newbie to Alteryx and I can't figure out what I am doing wrong. I am trying to do some very simple Imputations by changing null values in a field set to double double that has Nulls to impute the average on the cells with Nulls. When I do it it doesn't impute the few cells instead it changes everything to Null in the field. 

Please let me know where I am going wrong or please point me to the resources I can use because I can't find anything in the documentation that addresses this issue. Thanks!

Dan

[NicoleJohnson]

Can you perhaps attach an example of the starting data you're working with (dummy data is fine), your expected (correct) results, and then the incorrect results you are currently seeing? That should help us better troubleshoot and provide some suggestions! Thanks!

NJ

[NicoleJohnson]

Actually, took a look... I think there are two options:

Option 1: There is an Imputation tool in the Preparation category of tools (not sure if you're using this already?), but if you select the field you wish to impute with the average of that field, and then select Average from the Incoming Values to Replace options, that should give you what you need?

Option 2: Much longer, and results in the same answer as the above Option #1... but still an option :)

1. Summarize the values in your double field with a Summarize tool, using the AvgNo() option (which will give you the average of all the non-Null cells)

2. Append this average to your original dataset with an Append tool

3. Use the formula tool to replace nulls with the average value: IF ISNULL([Field1]) THEN [AvgNo()_Field1] ELSE [Field1] ENDIF

If either of those options help, let me know! Otherwise, let us know what additional tweaking might be needed! Sample data is always helpful, per my earlier comment... Thanks!

Cheers,

NJ

[danielreedsmith]

Thanks Nicole,

I got a work around to do it but I want to figure out the Imputation  tool because it would be much easier if it worked. I have attached a couple of screen shares. Its really basic that's why its confusing why its not working. I have two fields based on customer Dunn and Bradstreet sales size and # of Employees. Some of the customers do not have nulls so I just want to impute the average for the analysis I am doing.

The first screen shot shows the two tool flow and the data coming in showing there are only a couple of Nulls.

The second shot show after I run the flow with the imputation tool on the just those to columns and how its Nulling everything in those columns.

The third is just a screen shot of the Imputation tool settings showing I am only selecting those columns and wanting to impute the mean.

Those fields at this step are both double double for data type.

[NicoleJohnson · Accepted solution]

OK this seems like an odd one, but I think it has to do with your column names. Can you check to see if after you run it, there are two new columns that are titled Company___of_Employees and Company_Sales_Size?

I think what it's doing (for reasons I'm not sure about) is taking issue with the column names and creating new ones that replace the special character (#) and spaces with _. See snapshot attached. So I believe the imputation is working... it's just sticking them in a new column that you may not have noticed since you were looking for the results to show in the original one :) So you could put a Select tool after the Imputation tool, rename the new columns, and get rid of the original (now null) ones.

Let me know if that looks to be what is happening on your end too?

NJ

[danielreedsmith]

That was it!! Thank you and thank you for the possible reason as well!

Thanks again for the help!

Dan