How to Find the First Non-Null Value in a Column

Sigma users often want to return the first non-null value in a column. In this article we'll clear up a common misconception about how to accomplish this, and then we'll take a walk through the 'proper' implementation.

 

Sample Dataset

Here we have a sample dataset of 911 calls, grouped by the Incident ID.

mceclip0.png

 

Why Can't We Use Coalesce?

For each Incident ID we'd like to see what the most recently dispatched agency was. One misstep that users commonly make is using Coalesce, which does return the first non-null value from the arguments provided, but does not have the ability to traverse a column's values. If we add a column beside [Incident ID] using Coalesce, the formula will return an asterisk indicating that multiple values have been identified:

mceclip1.png

 

Ok... Then What Is The 'Right' Way?

Instead, we'll first want to use ListAgg which will return a list of the non-null values in the grouping: 

mceclip2.png

Once we have this list, we need to extract the first value-- this can be accomplished using SplitPart. SplitPart takes in a string as its first argument, followed by a "separator" (a character sequence on which to split the string into separate pieces), and an index (indicating which piece of the string to return). The final result of the formula will look like:

SplitPart(ListAgg([Dispatched Agency]), ",", 1)

Let's break that down... First, we're passing the [Dispatched Agency] column into ListAgg, returning a comma separated list of all non-null values. Then, we're passing that list into SplitPart, telling it to split the string into pieces on each comma that appears in the list, then we're telling it to return the first piece from that list. The end result will look like this: 

mceclip3.png

 

Keep In Mind

If your dataset already contains comma separated values, we can pass in another optional argument to ListAgg to separate that list by a different character sequence:

mceclip4.png

In the screenshot above, you can see that the [Responding Agency] values now contain commas. This means that our original ListAgg function would split 'Police Department, Cental Precinct' into two values, which would cause SplitPart to only return 'Police Department'. We don't want that, so instead we'll adjust our formula to pass in ">>" as a separator for the ListAgg output and pass in ">>" as our separator argument to SplitPart. The resulting formula looks like this: 

SplitPart(ListAgg([Dispatched Agency], ">>"), ">>", 1)

Hope this clears up some of the confusion!