First Non-Null Value from Column in Formula Tile

willrnt

Greetings DOMO,

Does anyone know how to find the first non-null value of a column in a formula tile. Tried using Coalesce along with a few tricks and couldn't get anything to work. All help would be appreciated.

Cheers,

Will

MichelleH

@willrnt To find the first non-null value, you will need to use a Group By tile instead of the formula tile since you want to look across multiple rows. You should be able to select first non-null value in the aggregation type drop down. Coalesce looks across multiple columns within the same row.

willrnt

@MichelleH Thanks for the feedback. Unfortunately I need it across all the rows and not for some specifically chosen set of group by columns. Hoping someone out there knows a trick.

MichelleH

@willrnt Try adding a constant value in the before your Group By tile and use that as your only group by column. Since the entire field is a constant, then it will return a single value as the first non-null value in your dataset.

SpencerM

Magic Downfill Steps.pdf

@willrnt

I have attached steps to complete this in Magic.

It is based on this approach for a Down Fill in T-SQL:

https://www.oraylis.de/blog/2016/fill-down-table-in-t-sql-last-non-empty-value