Next value against current value

[Poojashri]

Hi All,

I have data like this

Mno                           Start

1	07:30
1	08:10
1	08:30
1	09:15
1	09:47
1	10:35
1	11:22
1	13:05
1	13:41
1	14:15
1	15:40
1	16:15
2	11:07
2	16:59
2	13:30
2	11:51
2	13:19
2	07:27
2	08:22
2	08:33
2	17:21
2	13:41
2	15:09

I want to get a new column which will populate next value of start in new column with respect to each Mno.

Mno	start	Next_start
1	07:30	08:10
1	08:10	08:30
1	08:30	09:15
1	09:15	09:47
1	09:47	10:35
1	10:35	11:22
1	11:22	13:05
1	13:05	13:41
1	13:41	14:15
1	14:15	15:40
1	15:40	16:15
1	16:15	00:00
2	11:07	16:59
2	16:59	13:30
2	13:30	11:51
2	11:51	13:19
2	13:19	07:27
2	07:27	08:22
2	08:22	08:33
2	08:33	17:21
2	17:21	13:41
2	13:41	15:09
2	15:09	00:00

I tried it with below expression but i am not getting expected result.

Load *,

if (Mno=previous(MNo),peek('Start'),0) as Next_Stop  resident tab1 order by Mno desc

could someone please help me on this.

Thanks in advance.

Regards,

Poojashri

[brunobertels · Accepted solution]

Hi 

try this below. 

The trick here is because we are sorting Start in descending order your previous function will act as next.

[Table]:

LOAD * INLINE
[
Mno,Start,
1,07:30
1,08:10
1,08:30
1,09:15
1,09:47
1,10:35
1,11:22
1,13:05
1,13:41
1,14:15
1,15:40
1,16:15
2,11:07
2,16:59
2,13:30
2,11:51
2,13:19
2,07:27
2,08:22
2,08:33
2,17:21
2,13:41
2,15:09
](delimiter is ',');

[Table]:
load
Mno as Mno1,
Start as Start1,
if(previous(Mno)=Mno,previous( Start ),0) as Next_Stop
resident [Table] order by Mno,Start Desc;

resulting table : 

hope it helps 

Regards 

[Poojashri]

Thanks for the solution,

I was doing such a silly blunder 🙂