Question

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

  • 16 October 2018
  • 4 replies
  • 464 views

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.





4 replies

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?

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.

Userlevel 3

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))

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.

Reply