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

[PaulT]

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.

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.

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.

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.

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.

[DultonM]

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!