Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How to omit a record if any of the fields are null?

Alteryx Alumni (Retired)


I have a table of sales data with each column being a week's worth of sales. I only want records that have data in each of those fields and want to filter out all records that have Null values. How can I do this?


There are two basic elements necessary to make this happen. The first is that all records in the original table have a unique ID. If you do not have a unique ID in your data, go ahead and add a Record ID Tool.


Sample Data.png


In the sample data you can see we will want data from Rows 1 and 6 while filtering out each of the other records because they containnull values.


From here we will use the Transpose Tool to pivot your data into 3 separate columns. In the transpose field choose your unique ID as the KEY FIELD and make sure all other records are selected as DATA FIELDS.


Sample Transpose.png


The result is that you will have your unique ID field, a field called [Name] which contains the names of each of the fields in your data, repeated for every unique ID in your original data, and a [Value] field which contains the individual values for each of the records for each of the columns in the original data.


Now we want to search for Nulls, and get a comprehensive list of the UniqueID values that do not containNullvalues. Now is the time to bring in a Summarize tool and GroupBy your unique ID field, and then use the CountNull action.


Summarize Sample.png


The result is a list of how manynulls exist in each of your unique ID groups.


Next we can simply filter out the fields that have 0null values in them and then use the unique IDs to join back to the original data, and pull only those records.


Join Sample 2.png Sample Join.png


It's important to note here that because I'm only interested in the original fields I intentionally chose to deselect the unique ID and the Null Count fields from the output of the join so that I am left with only those records that have data in all of the weeks.


See the attached v10.5 workflow for an example of the approach above.

11 - Bolide

Great post! A simple solution to a common need. When I read this, I couldn't help but remember a handy trick that answers this question if the columns you wish to check remain static. Simply use a Filter tool with the expression: Null() in ([Field1],[Field2],[etc.]). A bit different than the usual use of a filter (i.e. field = values), but very powerful!