Iterating Through Table Calculated Non-Null Rows to Calc Diff_Seconds Between 2 Columns


(Simon Holstein) #1

I have two columns that are table calculations (If statements) based on returned measures.

The number of rows between non-null values in these two columns will vary.

What I need to do is measure the difference in seconds between the downtime_start column and downtime_stop column by iterating through rows until the column value is not null. It should always be the case that a downtime_start occurs before a downtime_stop. Any help would be greatly appreciated.


(Desiree) #2

Hi Simon,

It appears that both table calculations are outputting dates. Have you tried the Looker diff_seconds (start_date, end_date) function for this yet?


(Simon Holstein) #3

Yes, I will want to use diff_seconds, but first I need to iterate through rows to find non-null values to compare in the Diff_seconds function.


(jeffrey.martinez) #4

Hey Simon,
I wanted to share a pattern I’ve used to find the first non-null value for a column. It may be useful to you (and may also be improved on, I’m sure) but here it is to find the first non-null record in column orders.count:

max(
  if(
    row()=min(match(${orders.count}, ${orders.count})), 
      ${orders.count}, 0))

(Simon Holstein) #5

Thank you for this Jeffrey. My project has shifted a bit and I am not sure I will need an answer on this anymore. I appreciate the help.