Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
avinashlaldas
Contributor
Contributor

Difference of timestamp in next rounded hour

I want to convert the time into hour so that it rolls up to next hour.See example below.

Please help me out

Time1                                             Time2                                              Desired output(Hour)

12/12/2000 09:00:40 AM             12/13/2000 09:15:40 AM                   25

12/12/2000 09:00:40 AM             12/12/2000 09:18:40 AM                   1

12/13/2000 09:00:20 AM             12/13/2000 09:00:30 AM                   1

12/13/2000 09:40:40 AM             12/13/2000 11:00:40 AM                    2

12/13/2000 09:40:40 AM             12/13/2000 11:00:40 PM                    13

1 Solution

Accepted Solutions
petter
MVP
MVP

Here is a way of rounding up - although this leaves you with 14 hours on the last row instead of 13:


DATA:

LOAD

  *,

  Interval(Ceil(Time2-Time1,1/24)) AS Hours

INLINE [

Time1,Time2                                     

12/12/2000 09:00:40 AM,12/13/2000 09:15:40 AM

12/12/2000 09:00:40 AM,12/12/2000 09:18:40 AM

12/13/2000 09:00:20 AM,12/13/2000 09:00:30 AM

12/13/2000 09:40:40 AM ,12/13/2000 11:00:40 AM

12/13/2000 09:40:40 AM,12/13/2000 11:00:40 PM

];




View solution in original post

2 Replies
petter
MVP
MVP

Here is a way of rounding up - although this leaves you with 14 hours on the last row instead of 13:


DATA:

LOAD

  *,

  Interval(Ceil(Time2-Time1,1/24)) AS Hours

INLINE [

Time1,Time2                                     

12/12/2000 09:00:40 AM,12/13/2000 09:15:40 AM

12/12/2000 09:00:40 AM,12/12/2000 09:18:40 AM

12/13/2000 09:00:20 AM,12/13/2000 09:00:30 AM

12/13/2000 09:40:40 AM ,12/13/2000 11:00:40 AM

12/13/2000 09:40:40 AM,12/13/2000 11:00:40 PM

];




View solution in original post

avinashlaldas
Contributor
Contributor
Author

Thank you,it works for me.