Reply
New Contributor
Posts: 2
Registered: ‎06-29-2016
Accepted Solution

Forward filling and back filling from adjacent column

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
Cloudera Employee
Posts: 437
Registered: ‎07-29-2015

Re: Forward filling and back filling from adjacent column

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.

New Contributor
Posts: 2
Registered: ‎06-29-2016

Re: Forward filling and back filling from adjacent column

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

Highlighted
Cloudera Employee
Posts: 16
Registered: ‎12-19-2013

Re: Forward filling and back filling from adjacent column

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.