Knowledge Drop

In a table calculation, how do I fill all of the null values of a column with the last non-null value of that column before the current row?

  • 6 April 2021
  • 0 replies
  • 1629 views

Userlevel 5

Last Tested: Jan 21, 2019

 

index(${calculation_1}, max(offset_list(if(is_null(${calculation_1}), null, row()), -1 * row() + 1, row())))

 

There are a few steps in this formula:

  1. Get the row number for each non-null row using the row() function
  2. Use offset_list to get a list of all of those row numbers from the first row to the current row
  3. Take the max of that list of values, which will be the row number of the last non-null row
  4. Use the index function to get the value of the field for that row number

Screen Shot 2018-11-21 at 1.43.23 PM.png
 

 

This content is subject to limited support.                

 


0 replies

Be the first to reply!

Reply