Loop through Table and create column

[brindlogcool]

Hi,

How to create the new column by looping through an table in the Load script.

  Consider the below example

i have this table

Key                                                            Name   StartTime                  EndTime              Timestamp                 Type

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:36      Started

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:38      Started1

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:40      Started2

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:42      Started3

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:44      Started4

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:50      Started6

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:41      Started

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:43      Started1

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:44      Started2

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:46      Started3

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:46      Started4

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:50      Started6

I want to create the new table with additional column duration as bringing the next row

Key                                                            Name   StartTime                  EndTime              Timestamp                                     Type     Duration

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:36      Started     12/3/2013 12:12:38

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:38      Started1   12/3/2013 12:12:40

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:40      Started2   12/3/2013 12:12:42

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:42      Started3   12/3/2013 12:12:44

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:44      Started4   12/3/2013 12:12:50

A-12/3/2013 12:12:35-12/3/2013 2:12:35          A       12/3/2013 12:12:35  12/3/2013 2:12:35  12/3/2013 12:12:50      Started6   12/3/2013 12:12:50

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:41      Started      12/3/2013 12:12:43

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:43      Started1     12/3/2013 12:12:44

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:44      Started2    12/3/2013 12:12:46

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:46      Started3    12/3/2013 12:12:46

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:46       Started4   12/3/2013 12:12:50

B-12/3/2013 12:12:35-12/3/2013 2:12:40          B       12/3/2013 12:12:35  12/3/2013 2:15:40  12/3/2013 12:12:50      Started6    12/3/2013 12:12:50

I understand we can get this done using the Peek function but couldnt get it done.it would very helpful if somebody could help me on this, I got stuck on this last two days:)

[Anonymous]

Look this..

T:

load * Inline
[
key,endtime,timestamp

A-12/3/2013 12:12:35-12/3/2013 2:12:35,12/3/2013 2:12:35,12/3/2013 12:12:36
A-12/3/2013 12:12:35-12/3/2013 2:12:35 ,12/3/2013 2:12:35,12/3/2013 12:12:38
B-12/3/2013 12:12:35-12/3/2013 2:12:40,12/3/2013 2:15:40,12/3/2013 12:12:41
];

T2:
Load *,
peek(key,RowNo(),'T')as peek,
if(key=peek(key,RowNo(),'T'),peek(timestamp,RowNo(),'T'),endtime) as Duration
Resident T;

DROP Table T;

HTH,

Ravi