Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

kjmahesh
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

Tags (1)
3 Replies
MK_QSL
Not applicable

Re: Scripting

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

];

kjmahesh
Not applicable

Re: Scripting

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
Not applicable

Re: Scripting

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 ...