Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Display Null values as previous value until next non-null value is encountered

  • 16 April 2018
  • 2 replies
  • 1054 views
N

Hi,


Can someone please help:


I am trying to replace consecutive null values in a pivot by a value from the row above null. I tried using offset function but it fills up only for the next immediate value and skips the rest. Any help is appreciated.


Thanks

2 replies

philip.martinelli
Rank
Userlevel 2

Hey @npinto,


Please see my steps and screenshots below:


steps:



  • return row number for non null values

    if(coalesce(${users.count},0) != 0,row(),null) -> titled row_num

  • built list of all row numbers above (and including) each row

    offset_list(${row_num},-count(row())+1,count(row())) -> titled calculation 3

  • return measure value using the index of the last non null value’s row number (ie the max of the above-mentioned list)

    index(${users.count},max(${calculation_3}))



Thanks,

Philip

N

Sweet! That worked perfect. Thank you very much Philip!

Reply


Terms of Service