To populate the values from another column based on serial no

Analytics
Enthusiast

To populate the values from another column based on serial no

Hello All,

 

My table as below:

SNOCOL 1COL 2
9A1M
10A1N
11A1F
8A1H
12A1Q

 

Am expecting output as below:

COL 1COL 2NEXT VALUE
A1MN
A1NF
A1FQ
A1HM
A1QNULL

 

Next value is the new column to be populated based on sequence number of col 2.

In expected output, first row N comes with below logic:

Col 2 1st row value is M. Its corresponding sequence number is 9. So, 1st row next value should be populated with seq number 10's column 2 value which is N.

 

I tried using coalesce min with partition by row preceeding but am not getting the expected output.

 

Would be more helpful if someone can give the SQL query for the same.

 

Thanks in advance!

 

Tags (1)

Accepted Solutions
Senior Supporter

Re: To populate the values from another column based on serial no

good old outer join can do

select c.col1, c.col2, n.col2
from mytab c
        left outer join
        mytab n
           on c.sno + 1 = n.sno
                and c.col1 = n.col1

or an olap function

select col1, col2, max(col2) over (partition by col1 order by sno rows between 1 following and 1 following)
from mytab
1 ACCEPTED SOLUTION
7 REPLIES
Senior Supporter

Re: To populate the values from another column based on serial no

good old outer join can do

select c.col1, c.col2, n.col2
from mytab c
        left outer join
        mytab n
           on c.sno + 1 = n.sno
                and c.col1 = n.col1

or an olap function

select col1, col2, max(col2) over (partition by col1 order by sno rows between 1 following and 1 following)
from mytab
Highlighted
Enthusiast

Re: To populate the values from another column based on serial no

Thanks in ton Ulrich

Re: To populate the values from another column based on serial no

Hi,

 

I am looking for the similar solution but doesnt work in my case.

Below is the sample data

 

S.noIDVal_leg1Val_leg2
11234100120
11235??
21296150200
21270??

 

Expected output: If for ID 1235 Val_leg1 and Val_leg2 is null then take value from previous Val_leg2 as below

 

S.noIDVal_leg1Val_leg2Value
11234100120100
11235??120
21296150200150
21270??200

Thanks

Junior Contributor

Re: To populate the values from another column based on serial no

Re: To populate the values from another column based on serial no

Thanks for the quick response. 

I tried.

Its picking the last value from entire data when val_leg1 null but it should pick val_leg2 when val_leg1 is null for that Id w.r.t S.no

 

 

Teradata Employee

Re: To populate the values from another column based on serial no

In your example, for S.No 1 you have to order by ID asc, but int S.No 2 you have to order by ID desc.

Is it a typo ?

If yes, try something like :

create multiset volatile table mvt_data, no log
( serial_no     byteint
, ID            smallint
, Val_leg1      smallint
, Val_leg2      smallint
)
primary index (serial_no)
on commit preserve rows;

insert into mvt_data values (1, 1234,  100,  120);
insert into mvt_data values (1, 1235, null, null);
insert into mvt_data values (2, 1270,  150,  200); -- I switched 1296 and 1270
insert into mvt_data values (2, 1296, null, null);

  select serial_no, ID, Val_leg1, Val_leg2
       , coalesce(Val_leg1, Val_leg2, min(Val_leg2) over(partition by serial_no order by Id asc rows between 1 preceding and 1 following)) as "Value"
    from mvt_data
order by serial_no asc, ID asc;

Re: To populate the values from another column based on serial no

Hi,

yes, it was a typo.

I tried with the your suggestions, and I am getting expected data.

 

Thanks so much!