topic Re: Scripting in QlikView

Scripting

Anonymous — Wed, 23 Nov 2016 13:25:25 GMT

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

Re: Scripting

MK_QSL — Wed, 23 Nov 2016 13:33:43 GMT

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
];

Re: Scripting

Anonymous — Wed, 23 Nov 2016 13:39:13 GMT

Hi manish

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

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')

Re: Scripting

MK_QSL — Wed, 23 Nov 2016 13:49:40 GMT

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