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]

try something like

=interval( min({<flag = {'suspended'}>} datefield, 2) - min({<flag = {'inprogress'}>} datefield, 2) )

So min(FIELD, 2) should get you the second lowest value of FIELD

edit:

I assumed that your standard timestamp format set at the top of your script matches your datefield format. If not, you should adapt the standard timestamp format or use timestamp#(datefield, 'DD-MMM-YYYY hh:mm:ss') to interprete your field values correctly as timestamp.

[#3]

thanks. I will try and ask if needed some more help.

[#4]

thanks. I will try and ask if needed some more help.

[#5]

Hi,

Swuehl's answer is correct.

Maybe you could try this for a more specific format.

=interval(Date(min({<flag = {'suspended'}>} Date#(datefield,'DD-MMM-YYYY hh:mm:ss'),2),'YYYY-MM-DD hh:mm:ss')-

Date(min({<flag = {'inprogress'}>} Date#(datefield,'DD-MMM-YYYY hh:mm:ss'),2),'YYYY-MM-DD hh:mm:ss'),'hh:mm:ss')

Regards,

Janzen

[#6]

I have a similar query.

Here there is some flag and datefield. The flag becoming no to yes and vice versa.In this case how can I get the difference  b/w minimum second "no" and minimum first "yes".  i.e 24-sep-2012 6:30:23  -   24-sep-2012 3:50:23

flag                   datefield

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

[#7]

In the post before added, from that I just want to know that how to get the second no after yes.

This needs to be done at script level. Suggest me.

[#8]

Here there is some flag and datefield. The flag becoming no to yes and vice versa.In this case how can I  capture  the date 24-sep-2012 6:30:23 i.e second no after yes.

flag                   datefield

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     ----> How can i capture it in script. What is the query needs to be written. Please help.

No         24-sep-2012 7:30:23

[#9]

JeopardyAuditTemp:Load date(min(AuditTimeStamp),'DD/MM/YYYY h:mm:ss[.fff] TT') as MinAuditTimeStamp0 ,
     W6Key,
     Audit_AssignedEngineer
      FROM
[$(vAppQvdPath)\Audit$(vTableSuffix).qvd]
(qvd)where Audit_InJeopardy='No' Group by W6Key,Audit_AssignedEngineer,Audit_InJeopardy;

Currently I am capturing minimum AuditTimeStamp of first "No"  i.e  24-sep-2012 2:30:23.

But I need to capture mininmum AuditTimeStamp of Second  "no".  i.e 24-sep-2012 6:30:23   

How do get second no please suggest. This i need to get at script level itself.

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     ----> How can i capture it in script.

  No                             24-sep-2012 7:30:23

[CELAMBARASAN · Accepted solution]

try with this

JeopardyAuditTemp:

Load * Where counter=2;

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

      if((previous(W6Key & Audit_AssignedEngineer & Audit_InJeopardy)<>(

W6Key & Audit_AssignedEngineer & Audit_InJeopardy),1,peek('counter')+1) AS counter,

     Audit_AssignedEngineer
      FROM
[$(vAppQvdPath)\Audit$(vTableSuffix).qvd]
(qvd)where Audit_InJeopardy='No' Group by W6Key,Audit_AssignedEngineer,Audit_InJeopardy;