Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
djaykumar
Contributor
Contributor

Datetime Calculation

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

Labels (1)
4 Replies
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'))

 

Annotation 2020-08-21 100936.png

djaykumar
Contributor
Contributor
Author

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
Former Employee
Former Employee

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

Cheers,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
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