SQL Code to return the 'previous' entry if null

Looker Q&A Looker
1

Hi !

I have a recruitment table that for a particular job maps job stages to milestones. The below is an example

image.png
image.png803×174 4.85 KB

For example if the candidate is in the “Application Review” stage they are at the “Application” milestone

However if they are in the “Second Stage” for example the milestone is blank and I need to return the previous milestone entry based on the job stages order. So in this example I need to return the milestone as “Assessment”. The table is linked on “Jobs Stages Job ID” and “Jobs Stages Stages ID”

I’m struggling to work out how to do this in SQL so any help/advice would be much appreciated.

Kind regards,

Richard

2

Hi WorthyWow,

The function you are looking for is called “COALESCE”. It returns the first non-null value if found and null otherwise.

There are many examples of how to use this in SQL online.
Hope this helps!
Jasper

3

Check out sql lag window function, coalesce won’t work across rows. You would be better just creating a mapping table to fill the gaps though if it is a consistent process.

1 Like
4

Hi - many thanks for the suggestions - much appreciated.

5

Hi again,

I came across this post, I think it is exactly what you need!

[Display Null values as previous value until next non-null value is encountered](https://discourse.looker.com/t/display-null-values-as-previous-value-until-next-non-null-value-is-encountered/7644) Table Calculations

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

Hope this helps,
Jasper

1 Like
6

Hi Jasper - wow ! That looks just what I’m after! I’ll report back once I’ve given it a go.

Many thanks,

Richard