Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Scripting

I have date  fields  like start date and end date in my table

start date                                  end date

2016-10-23 00:00:00:00             2016-10-24 23:58:59:00

2016-11-24 00:00:00:00             2017-01-10 23:59:59:00

2016-12-01 00:00:00;00            2016-12-31 23:58:59:00

2016-12-28 00:00:00:00            2017-05-1 00:00:00:00

2017-01-01 00:00:00:00             2017-08-08 23:58:58:00

i want the  total hours between these date range and i want to this in the script level

for Example :  start date:2016-10-23 00:00:00:00 and end date: 2016-10-24 23:58:59:00  i want as total hours spent was 48 hours

can any one help me on this

Thanks

Mahesh

3 Replies
MK_QSL
MVP
MVP

Data:

Load *, DifferenceInTime * 24 as Difference;

Load

  Interval(TimeStamp#([end date],'YYYY-MM-DD hh:mm:ss:ff') - TimeStamp#([start date],'YYYY-MM-DD hh:mm:ss:ff')) as DifferenceInTime,

  TimeStamp#([start date],'YYYY-MM-DD hh:mm:ss:ff') as [start date],

  TimeStamp#([end date],'YYYY-MM-DD hh:mm:ss:ff') as [end date]

Inline

[

  start date,             end date

  2016-10-23 00:00:00:00, 2016-10-24 23:58:59:00

  2016-11-24 00:00:00:00, 2017-01-10 23:59:59:00

  2016-12-01 00:00:00;00, 2016-12-31 23:58:59:00

  2016-12-28 00:00:00:00, 2017-05-1 00:00:00:00

  2017-01-01 00:00:00:00, 2017-08-08 23:58:58:00

];

Anonymous
Not applicable
Author

Hi manish

Thank you for fast response .  can you please explain these syntax

  1. Data: 
  2. Load *, DifferenceInTime * 24 as Difference; 
  3. Load 
  4.   Interval(TimeStamp#([end date],'YYYY-MM-DD hh:mm:ss:ff') - TimeStamp#([start date],'YYYY-MM-DD hh:mm:ss:ff')
MK_QSL
MVP
MVP

1) I have converted start date and end date into timestamp using TimeStamp# function.

Read below blog post to understand difference between TimeStamp and TimeStamp#.

QlikView Date fields

2) Now you can find the difference between them. In case if we need to show more than 24 hours, use Interval function.

This will give you difference in hh:mm:ss format.

3) Now multiply the result by 24 which will convert it to hours ...