topic Re: Time difference between two rows in QlikView

Time difference between two rows

Tue, 17 Mar 2015 10:37:09 GMT

Hi there

I'm trying tow calculate the time difference between two rows. Below is my script for the two fields. Im also sorting correctly, but still dont get the proper durations. PLEASE HELP !!!!???

INCIDENT_MAIN_NEW:

LOAD

If(ParentLink_RecID = Peek(ParentLink_RecID), Peek(CreatedDateTime_Step) - CreatedDateTime_Step

,If(Status = 'CLOSED', Interval(ClosedDateTime - CreatedDateTime_Step, 'D hh:mm'), Interval(NOw(1) - CreatedDateTime_Step, 'D hh:mm')

)

) As Step_Duration,

If(ParentLink_RecID = Peek(ParentLink_RecID), Peek(CreatedDateTime_Step) - CreatedDateTime_Step

,If(Status = 'CLOSED', Interval(ClosedDateTime - CreatedDateTime_Step, 'D'), Interval(NOw(1) - CreatedDateTime_Step, 'D')

)

As Step_Duration_Days

Resident INCIDENT

ORDER BY IncidentNumber, CreatedDateTime_Step ;

Re: Time difference between two rows

sunny_talwar — Tue, 17 Mar 2015 10:40:35 GMT

Would you be able to give an example (may be couple of rows of data) with what you got and what you expect to get.

Best,

Re: Time difference between two rows

Tue, 17 Mar 2015 10:47:47 GMT

Hi S

Here is ONE incident. Im getting rubbish data back.

IncidentNumber	CreatedDateTime_Step	Step Duration	Step_Duration_Days
724695	22/12/2011 13:27		-0.16685185185634
724695	22/12/2011 13:30		-0.0025231481413357
724695	22/12/2011 09:27		1181
724695	01/12/2012 12:33		-

Re: Time difference between two rows

Roop — Tue, 17 Mar 2015 11:04:19 GMT

Try a simpler version to start with to return the variance in seconds:

LOAD Num,

Date,

Date - peek(Date) as Datevar,

24*60*60*(Date - peek(Date)) as DatevarSec

.....

etc

This seems to work with your figures.

Re: Time difference between two rows

Tue, 17 Mar 2015 12:05:16 GMT

Thanks Rupert

I've given that a try. BUt no luck. I might be doing something wrong somewhere

Re: Time difference between two rows

Roop — Tue, 17 Mar 2015 12:06:58 GMT

Can you include your script that you are trying again?

Thanks

Re: Time difference between two rows

rubenmarin — Tue, 17 Mar 2015 12:15:45 GMT

Hi Bradley,

This: Peek(CreatedDateTime_Step) - CreatedDateTime_Step

Should be: CreatedDateTime_Step - Peek(CreatedDateTime_Step)

And not sure but maybe ParentLink_RecID should be on Order By.

Or change the "If(ParentLink_RecID = Peek(ParentLink_RecID)" to

If(IncidentNumber=Peek(IncidentNumber)

Re: Time difference between two rows

maxgro — Tue, 17 Mar 2015 12:23:43 GMT

try this

load *, date#(CreatedDateTime_Step, 'DD/MM/YYYY hh:mm') as d inline [

IncidentNumber, CreatedDateTime_Step, Step Duration, Step_Duration_Days

724695, 22/12/2011 13:27, -0.16685185185634

724695, 22/12/2011 13:30, -0.0025231481413357

724695, 22/12/2011 09:27, 1181

724695, 01/12/2012 12:33, -

];

load

d - Peek(d) as Duration,

floor(d - Peek(d)) as DurationD,

interval(frac(d - Peek(d))) as DurationS

Resident a

order by d;

DROP Table a;