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:)

[its_anandrjs]

How you calculate "Duration" let me know.

[#3]

What logic you used to find the Duration time  in your expected output table field Duration

[brindlogcool]

Duration is the Next Row value, I will check the Current row Key field value to next row Key Field value . If both are same then Duration will be the Next row Timestamp Value else EndTime Column Value.

Logically it will be like this

IF(Key=Peek('Key',RowNo()+1),Peek('Timestamp',RowNo()+1,EndTime) as Duration

[CELAMBARASAN]

Hi,

Create a rownumber field in the Raw data like

Load

     RowNo() AS RowNumber

     *....

then use Order by over the field RowNumber in descending and get the values using Previous function

Load

     *,

     Previous(Timestamp) AS Duration...

[brindlogcool]

Hi Celambarasan,

Thanks for the response. But it is not always the next row value you will need compare the key value and if it doesnt match then you should have the endtime as duration else the nextrow value..Logically like this

IF(Key=Peek('Key',RowNo()+1),Peek('Timestamp',RowNo()+1,EndTime) as Duration

[Anonymous]

check this..

[brindlogcool]

Is there any link you attached or sample file... I couldnt see anything ...

[Anonymous]

Now? I attached a .qvw file.

[brindlogcool]

Sorry still i couldnt see any files attached to it