Please help

[#1]

If we have to fetch second value from no of values. How to get it.

Fr example..

I have a file with date 24-sep-2012 timestamps. here there is some flag. The flag becoming suspended to inprogress.In this case how can i get the difference

b/w second suspened and second inprogress.

flag                     datefield

dispatched          24-sep-2012 2:30:23

delivered             24-sep-2012 3:30:23

inprogress           24-sep-2012 3:50:23

completed           24-sep-2012 4:30:23

suspended          24-sep-2012 5:30:23

inprogress           24-sep-2012 6:30:23

suspended          24-sep-2012 7:30:23

[swuehl]

You can try it like this, creating a group for each block of yes / no and filtering the no flags starting from the third block:

Set TimestampFormat = 'DD-MMM-YYYY h:mm:ss';

INPUT:

LOAD *,

if(peek(Audit_InJeopardy)=Audit_InJeopardy,peek(Group),rangesum(peek(Group),1)) as Group

  INLINE [

Audit_InJeopardy,     AuditTimeStamp

  No,                 24-sep-2012 2:30:23

  No,                 24-sep-2012 3:30:23

  Yes,                24-sep-2012 3:50:23

  Yes,                24-sep-2012 4:30:23

  Yes,                24-sep-2012 5:30:23

  No,                 24-sep-2012 6:30:23    

  No,                 24-sep-2012 7:30:23

];

JeopardyAuditTemp:

Load date(min(AuditTimeStamp),'DD/MM/YYYY h:mm:ss[.fff] TT') as MinAuditTimeStamp0

  //   W6Key,

  //   Audit_AssignedEngineer

      resident INPUT

      where Audit_InJeopardy='No' and Group > 2 // no matter if Audit_InJeopardy starts with yes or no

      Group by /*W6Key,Audit_AssignedEngineer,*/

              Audit_InJeopardy;

I just commented W6Key and Audit_AssignedEngineer out (no data for those fields and no idea what they mean).

Hope this helps,

Stefan