Find next not null value in the order

Analytics
Highlighted
Sei
Enthusiast

Find next not null value in the order

I have a table :

 

IDTimeStampValue
13null
14null
15B
16A
17null
18C
19A

 

For a given ID, with rows ordered by Timestamp, I want to fill null values with the next available non null value in following rows. The output should be in this manner.   

IDTimeStampValueNew_Val
13nullB
14nullB
15BB
16AA
17nullC
18CC
19AA

 

I tried using coalesce(min( value) over (partition by id order by timestamp rows between 0 following and 10 following) , null) as new_value but that is not giving the right answer and I see why. Please help me with the logic.

 

Thanks


Accepted Solutions
Sei
Enthusiast

Re: Find next not null value in the order

Got the answer. I used

first_value(value ignore nulls) over (partition by id order by timestamp rows between 0 following and UNBOUNDED FOLLOWING) as new_value

1 ACCEPTED SOLUTION
3 REPLIES 3
Sei
Enthusiast

Re: Find next not null value in the order

Got the answer. I used

first_value(value ignore nulls) over (partition by id order by timestamp rows between 0 following and UNBOUNDED FOLLOWING) as new_value

Teradata Employee

Re: Find next not null value in the order

Hi Sej,

 

You can also work in the descending order in this way :

last_value(Value ignore nulls) over (partition by id order by Timestamp desc)
Teradata Employee

Re: Find next not null value in the order

With the data :

create multiset volatile table mvt_data, no log
( Id    integer not null
, Ts    byteint not null
, Val   char(1)     null
)
primary index (Id)
on commit preserve rows;

insert into mvt_data values (1,  3, null);
insert into mvt_data values (1,  4, null);
insert into mvt_data values (1,  5, 'B' );
insert into mvt_data values (1,  6, 'A' );
insert into mvt_data values (1,  7, null);
insert into mvt_data values (1,  8, 'C' );
insert into mvt_data values (1,  9, 'A' );

collect statistics column (Id) on mvt_data;

 

In this case, the first_value / last_value solution will perform better, but here the nPath solution - for everyone to practice :

  select Id     as Id
       , Ts     as Ts
       , Val    as Val
       , NewVal as NewVal
    from nPath(          on mvt_data
               partition by Id
                   order by Ts asc
                      using
                       mode ( overlapping )
                    symbols ( Val is     null as V1
                            , Val is not null as V2
                            )
                    pattern ( 'V1*.V2?' )
                     result ( first(Id   of any(V1,V2)) as Id
                            , first(Ts   of any(V1,V2)) as Ts
                            , first(Val  of any(V1,V2)) as Val
                            , first(Val  of        V2 ) as NewVal
                            )
              ) as np
order by 1, 2;

the ? in V1*.V2? pattern is to handle case where last val is a null.