topic Get Date from previous value. in QlikView

Get Date from previous value.

Tue, 31 Mar 2015 14:18:53 GMT

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

Re: Get Date from previous value.

MK_QSL — Tue, 31 Mar 2015 14:27:10 GMT

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

Re: Get Date from previous value.

Anonymous — Tue, 31 Mar 2015 14:54:18 GMT

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;