How to get the next value of column.

[#1]

hi,

i have two  column as time and defect id.The defect id has different time and date.i need to show the start date and end date of defect id.

if there are different defect id for each defect id i need to calculate the start and end date

defect id    time

1              014-05-26 22:41:34

2              2014-05-28 05:59:11

1              2014-05-30 02:37:04

2              2014-05-26 00:07:52

1             2014-05-26 15:37:14

2              2014-05-26 22:41:34

3              2014-05-28 05:59:11

3             2014-05-30 02:37:04

etc

i need is:

defect id    time                                          start date                                end date

1              014-05-26 22:41:34                      014-05-26 22:41:34                  2014-05-30 02:37:04

2              2014-05-28 05:59:11                    2014-05-28 05:59:11                 2014-05-26 00:07:52

1              2014-05-30 02:37:04                    2014-05-30 02:37:04                  2014-05-26 15:37:14

2              2014-05-26 00:07:52                   2014-05-26 00:07:52                   2014-05-26 22:41:34

1             2014-05-26 15:37:14                    2014-05-26 15:37:14                   todays date

2              2014-05-26 22:41:34                   2014-05-26 22:41:34                     today date

3              2014-05-28 05:59:11                  2014-05-28 05:59:11                       2014-05-30 02:37:04

3             2014-05-30 02:37:04                  2014-05-30 02:37:04                       today date

if i will put filter on defect id say 1 is selected then it will show :

defect id          time                                    startdate                                   end date                  

1                     014-05-26 22:41:34                014-05-26 22:41:34                 2014-05-30 02:37:04

1                     2014-05-30 02:37:04              2014-05-30 02:37:04                2014-05-26 15:37:14

1                     2014-05-26 15:37:14                  2014-05-26 15:37:14              todays date

[MK_QSL]

Something like below

Script

=================================

Temp:

Load

  [defect id],

  Timestamp(time) as time,

  RowNo() as NO

Inline

[

  defect id, time   

  1,               2014-05-26 00:07:52

  1, 2014-05-26 07:15:32

  1, 2014-05-26 22:41:34

];

NoConcatenate

Final:

Load

  [defect id],

  time,

  time as StartDate,

  IF(NOT IsNull(Previous(time)),Previous(time),TimeStamp(Today())) as EndDate,

  NO

Resident Temp

Order By NO Desc;

  Drop Table Temp;

  Drop Field NO;

==========================================

NOW Create a straight table or table box with your required fields...

[#3]

it is  not working . I have already loaded two columns from excel sheet.

I tried with your code but is showing error after concatenate.

if possible please attach qvw file for reference.

[MK_QSL]

Check enclosed file..

[#5]

start date is coming correct but end date is not coming correct .

[MK_QSL]

everything is matching with your question ...

[#7]

if there are different defect id for each defect id i need to calculate the start and end date

defect id    time

1              014-05-26 22:41:34

2              2014-05-28 05:59:11

1              2014-05-30 02:37:04

2              2014-05-26 00:07:52

1             2014-05-26 15:37:14

2              2014-05-26 22:41:34

3              2014-05-28 05:59:11

3             2014-05-30 02:37:04

etc

i need is:

defect id    time                                          start date                                end date

1              014-05-26 22:41:34                      014-05-26 22:41:34                  2014-05-30 02:37:04

2              2014-05-28 05:59:11                    2014-05-28 05:59:11                 2014-05-26 00:07:52

1              2014-05-30 02:37:04                    2014-05-30 02:37:04                  2014-05-26 15:37:14

2              2014-05-26 00:07:52                   2014-05-26 00:07:52                   2014-05-26 22:41:34

1             2014-05-26 15:37:14                    2014-05-26 15:37:14                   todays date

2              2014-05-26 22:41:34                   2014-05-26 22:41:34                     today date

3              2014-05-28 05:59:11                  2014-05-28 05:59:11                       2014-05-30 02:37:04

3             2014-05-30 02:37:04                  2014-05-30 02:37:04                       today date

if i will put filter on defect id say 1 is selected then it will show :

defect id          time                                    startdate                                   end date                    

1                     014-05-26 22:41:34                014-05-26 22:41:34                 2014-05-30 02:37:04

1                     2014-05-30 02:37:04              2014-05-30 02:37:04                2014-05-26 15:37:14

1                     2014-05-26 15:37:14                  2014-05-26 15:37:14              todays date

[#8]

[MK_QSL]

Temp:

Load

  [defect id],

  TimeStamp(Timestamp#(time,'YYYY-MM-DD hh:mm:ss')) as time,

  Autonumber(RowNo()&[defect id]) as NO

Inline

[

  defect id, time  

  1,          2014-05-26 22:41:34

  2,          2014-05-28 05:59:11

  1,          2014-05-30 02:37:04

  2,          2014-05-26 00:07:52

  1,          2014-05-26 15:37:14

  2,          2014-05-26 22:41:34

  3,          2014-05-28 05:59:11

  3,          2014-05-30 02:37:04

];

NoConcatenate

Final:

Load

  [defect id],

  time,

  time as StartDate,

  IF([defect id] = Previous([defect id]),Previous(time),TimeStamp(Today())) as EndDate,

  NO

Resident Temp

Order By [defect id] asc, NO Desc;

Drop Table Temp;

[#10]

now for end it is coming today's date and time is 12:00:00pm for all the rows