topic Datetime Calculation in QlikView

Datetime Calculation

djaykumar — Sat, 16 Nov 2024 18:12:18 GMT

Hi,

I have two fields Startdate and enddate. From this I have to calculate difference between Startdate and enddate. And per day Hours should not exceeded to 9 hours. ie., max Duration is 9 hours

for example

Startdate Enddate

04-06-2020 02:00 PM 04-06-2020 05:00 PM
04-06-2020 03:00 PM 04-06-2020 05:00 PM
05-06-2020 07:00 AM 05-06-2020 06:00 PM
06-06-2020 09:00 AM 06-06-2020 05:00 PM
08-06-2020 09:01 AM 08-06-2020 04:01 PM
04-05-2020 07:14 AM 04-05-2020 04:00 PM
04-05-2020 09:00 AM 04-05-2020 12:00 PM
04-05-2020 01:30 PM 04-05-2020 03:30 PM

Result will be in Hours and minutes

Duration

03:00
02:00
11:00
08:00
07:00
08:46
03:00
02:00

I have gone through many ways but not get . In the above Duration should not be 9 hours like 3 rd row 11.

Please help me in this as I am in deep struggling to get

Thanks

Jay

Re: Datetime Calculation

Kushal_Chawda — Fri, 21 Aug 2020 09:12:46 GMT

LOAD *,RowNo() as Rec Inline [ Startdate , Enddate 04-06-2020 02:00 PM , 04-06-2020 05:00 PM 04-06-2020 03:00 PM , 04-06-2020 05:00 PM 05-06-2020 07:00 AM , 05-06-2020 06:00 PM 06-06-2020 09:00 AM , 06-06-2020 05:00 PM 08-06-2020 09:01 AM , 08-06-2020 04:01 PM 04-05-2020 07:14 AM , 04-05-2020 04:00 PM 04-05-2020 09:00 AM , 04-05-2020 12:00 PM 04-05-2020 01:30 PM , 04-05-2020 03:30 PM ];

Below is the expression

=only({<Rec={"=interval(Enddate-Startdate,'hh:mm')<='09:00'"}>}interval(Enddate-Startdate,'hh:mm'))

Re: Datetime Calculation

djaykumar — Fri, 21 Aug 2020 09:17:49 GMT

Hi Kush,

Thank you for quick reply

I want these calculation to be done in qlikview script while loading.

Please help me.

Thanks,

Jay

Re: Datetime Calculation

Brett_Bleess — Thu, 03 Sep 2020 15:15:38 GMT

@Kushal_Chawda Any further suggestions on this one, again, apologies for the notification issues we are having on the Community.

Cheers,
Brett

Re: Datetime Calculation

Kushal_Chawda — Thu, 03 Sep 2020 20:26:31 GMT

@djaykumar You can do something like below in script

LOAD *,

interval(if(interval(Enddate-Startdate,'hh:mm')<='09:00',Enddate-Startdate,0),'hh:mm') as Interval

FROM Source