community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

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

Alteryx Alumni (Retired)
Created on

Question

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?

Answer

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 contain null 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 contain Null values. 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 many nulls exist in each of your unique ID groups.

 

Next we can simply filter out the fields that have 0 null 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.

Attachments
Comments
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!