topic Re: Intervals between timestamps in hours with fractions of a day for interval ends in QlikView

Intervals between timestamps in hours with fractions of a day for interval ends

alex_bernea — Tue, 28 Mar 2017 17:00:38 GMT

Hello,

I am trying to break the interval between 2 timestamps in order to find out the difference in hours per day with fractions for intervals

I have the following input data:

StartTime	EndTime
02/08/17 20:44	02/10/17 04:52

I would like to show them as:

I tried while loops and intervals, but I can't find a way to break correctly the edges of the intervals (those that are not full day)

Thank you,

Alex

Anonymous — Tue, 28 Mar 2017 17:08:15 GMT

Try sing interval function explained here.

something like interval(Endtime-StartTime,'HH:MM')

sunny_talwar — Tue, 28 Mar 2017 17:54:12 GMT

Try this

Table:

LOAD *,

Interval(EndTime - StartTime, 'hh:mm') as [Time Elapsed];

LOAD TimeStamp(RangeMax(DayStart(StartTime + IterNo()-1), StartTime)) as StartTime,

TimeStamp(RangeMin(DayStart(StartTime + IterNo()-1, 1), EndTime)) as EndTime

While Floor(StartTime) + IterNo() <= Floor(EndTime + 1);

LOAD * INLINE [

StartTime, EndTime

02/08/17 20:44, 02/10/17 04:52

03/08/17 10:44, 03/10/17 17:52

];

alex_bernea — Wed, 29 Mar 2017 03:57:06 GMT

It works. Thannk you