Getting the First Record with a Non-Null Value in a Pivot Column or Pivot Row

[jeffrey_martine]

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

Pivoting results by a dimension in an Explore is a great way to organize results and visualizations in a more readable way. Sometimes, however, pivoted results can display quite a few null values in a column where there are no matching values for a row:

So what do we do if we don't want to have to wade through that sea of nulls just to find the first non-null value in a pivot column or row? This article discusses how to find that first non-null value by using table calculations.
 

Getting the First Non-Null Record in a Pivot Column

The following table calculation will return the first non-null value of a pivot column as a single constant value:

index(${history.query_run_count},min(if(NOT is_null(${history.query_run_count}), row(), null))) 

Here's how it works:

1. if(NOT is_null(${history.query_run_count}), row(), null)

   In this statement, the if() function evaluates each row one at a time. If the measure value in a given row is NOT NULL (NOT is_null()) then we output the cell's row number with the row() function. If the measure value is null, then the calculation outputs null.

2. min(if(NOT is_null(${history.query_run_count}), row(), null))

   We take the minimum of the list values provided in step 1 with the min() function, which outputs the lowest (first) row number in that column that has a non-null value.

   Note: if we wanted to find the last non-null value, we could simply change this step to use max().

3. index(${history.query_run_count}, min(if(NOT is_null(${history.query_run_count}), row(), null)))

   The index() function returns the value of the nth element of the column created by an expression, unless an expression defines a column of lists. If an expression defines a column of lists, index() returns the nth element of each list. Since we have already calculated which row has the first non-null value in steps 1 and 2, we plug the numerical value of min(if(NOT is_null(${history.query_run_count}), row(), null)) in for our value n, and now we have the first non-null value in our pivot column.

In a results table, the table calculation looks like this:

Getting the First Non-Null Record in a Pivot Row

The calculation below returns the first non-null value of a pivot row. The calculation will appear as a column to the far right of the pivoted results:

  pivot_where(min(pivot_row(if(NOT is_null(${history.query_run_count}), pivot_column(), null))) = pivot_column(), ${history.query_run_count})

Here's how it works:

1. if(NOT is_null(${history.query_run_count}), pivot_column(), null)

   Similar to the solution above, this if() statement evaluates each row one at a time. If a measure value is NOT NULL, then it outputs the cell's pivot column index with the pivot_column() function. If the measure is null, the output is null.

   Note: In Looker, pivot columns are numbered left to right starting from 1.

2. min(pivot_row(if(NOT is_null(${history.query_run_count}), pivot_column(), null)))

   pivot_row() makes a row-wise list of the values from step 1. This is an important step, because this makes sure that the min() is taking the row minimum, not the column minimum. This minimum gives us the lowest (first) pivot_column in that row that has a non-null value.

   Note: If we wanted to find the last non-null value, we could simply change this step to max().

3. pivot_where(min(pivot_row(if(NOT is_null(${history.query_run_count}), pivot_column(), null))) = pivot_column(), ${history.query_run_count})

   
   The pivot_where() function returns a list of the values from the pivot column that satisfies an expression, or null if such a column does not exist.
   
   Since we have already calculated which pivot column has the first non-null value for each row in steps 1 and 2, we need to specify a condition that points to the value in that row. We do this by checking when our current pivot column is equal to the value from steps 1 and 2. Where that expression is true, the calculation outputs the value of the measure.

In a results table, the calculation looks like this: