Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Replace null with average across multiple groups

Highlighted
Asteroid

Hi All,

 

Scenario :

 

I have a data output with two columns, "group ID" and "work time" - work time is manually filled in and some values are blanks.

 

What I'd like to do is replace the null values with averages for that group -  as an example:

Group | Time

1         | 5 

1         | 5

1         | [null]

2         | [null]

2         | 10

 

I'd like the null value to be replaced with the average of the groups.

 

Can someone explain how I can achieve this? I've experimented with imputation and multi field formula but am a bit stuck.

 

Cheers!

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

I'd use a summarize tool and group by the group field and calculate the average (without nulls) for each group.  You can join that back to your data and use a formula like:

 

IIF(ISNULL([TIME]),[AVG_TIME],[TIME])

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Alumni (Retired)

I always like to communicate the paradigm for solving a data problem in Alteryx is pretty much "as you think through the problem".

 

In this case, you want to...

  • Get the average for the values that are there (Summarize by group)
  • Filter out the records where the data is Null
  • Replace the Null value with the average (done by a Join)
  • Bring back in the original records that already had data (Union)
  • And then sort back to a group order.

 

I've attached an example of how this works.

Alteryx Alumni (Retired)

Beat me to it, @MarqueeCrew. :-)

My example is basically the same, but a bit different.

Labels