Get Date from previous value.

[#1]

Hello everyone,

im stuck in something that I think it shouldnt be to hard.

I have the follwing table

    Ref                    Date               Trail

REF1234	13/04/2014 10:40	Action
REF1234	13/04/2014 10:51
REF1234	13/04/2014 15:15
REF1234	13/04/2014 16:12
REF1234	13/04/2014 16:13
REF1234	14/04/2014 05:02	Raised

I would like to obtain the "previous" date of the when the Trail Field = Raised.

A more in depth explanation, once the field "Trail" = Raised, I want to obtain the previous date where the field "Trail" had a value, in this case i want my result to be "13/04/2014 10:40", which is the the most recent date prior to the field Trail was flagged as Raised and thethe field "Trail" had a value to it.

Many Thanks

[MK_QSL]

Temp:

Load *, If(IsNull(Trail) or Len(Trim(Trail))=0,1,0) as TrailNullFlag Inline

[

    Ref,        Date,               Trail

  REF1234, 13/04/2014 10:40, Action

  REF1234, 13/04/2014 10:51,

  REF1234, 13/04/2014 15:15,

  REF1234, 13/04/2014 16:12,

  REF1234, 13/04/2014 16:13,

  REF1234, 14/04/2014 05:02, Raised

];

Left Join

Load *, If(Ref = Previous(Ref) and Trail = 'Raised', Previous(Date)) as PreviousDate;

Load

  Ref,

  Date,

  Trail

Resident Temp

Where TrailNullFlag = 0

Order By Date

[Anonymous]

T1:

LOAD * ,recno() as ID INLINE [

Ref          ,          Date     ,          Trail

REF1234 ,13/04/2014 10:40, Action

REF1234, 13/04/2014 10:51 ,

REF1234 ,13/04/2014 15:15 ,

REF1234, 13/04/2014 16:12,

REF1234 ,13/04/2014 16:13,

REF1234 ,14/04/2014 05:02, Raised

];

T2:

load *,'' as Junk

Resident T1

where (Trail)<>'';

T3:

load *, Peek(Date) as PreDate

Resident T2;

DROP Table T2;

drop Field Junk;

left join(T1)

load * Resident T3;