question Re: Forward filling and back filling from adjacent column in Archives of Support Questions (Read Only)

Forward filling and back filling from adjacent column

addie — Fri, 16 Sep 2022 10:28:15 GMT

Is there a way to use something like Oracle's "IGNORE NULLS" with the last_value() analytic function (or similar)? I want to fill all the nulls in a column with the last value in that column that was non-null. In Oracle I can do something like the below. How can I implement this in Impala? I need to forward fill the null values if possible, and if the partition begins with null, I'd like to backfill from the first non-null value.

-- Oracle sample query -- 
select user, date, val, 
       case when val is null 
            then last_value(val ignore nulls)
                 over (partition by user order by date) 
            else val 
       end as new_val

Re: Forward filling and back filling from adjacent column

Tim Armstrong — Thu, 30 Jun 2016 15:53:35 GMT

I think we already have an open issue for this that is being actively worked on https://issues.cloudera.org/browse/IMPALA-3210

I.e. we don't support it yet but it's in the pipeline.

Re: Forward filling and back filling from adjacent column

addie — Thu, 30 Jun 2016 19:51:41 GMT

Thanks for the prompt response, Tim. Is there currently a way to achieve a similar functionality without this support?

Re: Forward filling and back filling from adjacent column

Matt Jacobs — Fri, 01 Jul 2016 02:05:41 GMT

We're working on it and it should be in the next release, but unfortunately we're not aware of a good way to simulate the same results.