Datetime Calculation

[djaykumar]

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

[Kushal_Chawda]

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

[djaykumar]

Hi Kush,

Thank you for quick reply

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

Please help me.

Thanks,

Jay 

[Brett_Bleess]

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

Cheers,
Brett

[Kushal_Chawda]

@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