topic Re: Difference Between Time Rows in QlikView

Difference Between Time Rows

Thu, 02 Apr 2015 19:57:34 GMT

Hi All,

Below is part of a table in my data model. I want to get the difference between each start/finish time in order (Row 2- Row 1, Row 4-Row3, etc...) 2:22:59 AM - 2:15:28 AM = X. I have tried several variations of set analysis to no avail. Please help!

Re: Difference Between Time Rows

MK_QSL — Thu, 02 Apr 2015 20:07:39 GMT

Easiest way is to calculate script level. Provide sample data in excel.. will do it for you..

Re: Difference Between Time Rows

Thu, 02 Apr 2015 20:16:27 GMT

Hi Manish,

Thanks you. See attached for sample data.

Re: Difference Between Time Rows

MK_QSL — Thu, 02 Apr 2015 20:46:55 GMT

Test:

Load

Type,

Time(Time#(Time,'h:m:ss TT')) as Time,

Record

Inline

[

Type, Time, Record

Start, 2:15:28 AM, 1

Finish, 2:22:59 AM, 2

Start, 3:15:28 AM, 3

Finish, 3:22:56 AM, 4

Start, 4:15:28 AM, 5

Finish, 4:23:00 AM, 6

Start, 5:15:28 AM, 7

Finish, 5:22:57 AM, 8

Start, 6:00:22 AM, 9

Finish, 6:00:43 AM, 10

];

Final:

Load

Type,

Time,

If(Type = 'Finish',Interval(Time-Previous(Time)),0) as Difference,

Record

Resident Test

Order By Record;

Drop Table Test;

Re: Difference Between Time Rows

Thu, 02 Apr 2015 21:17:59 GMT

Perfect, thank you, Manish.