topic Re: How to calculate difference between 2 time fields. in QlikView

How to calculate difference between 2 time fields.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have 2 time columns in 'hh,mm,Ss' format . which I loaded like this :

Time#(TIME_24_HOUR_HHMMSS_FORMAT) as call_start_time_24

Time#(WEB_END_TIME) as web_end_time;

I want to know How to calculate difference between and I want to know if that difference is less than 15 min.

I am tryting something like this :

if(interval(time(call_start_time_24,'hh:mm:ss')-time(web_end_time,'hh:mm:ss'),'mm')<=15 ,'Online prior to call - web session started and ended before the call - 15 mins').

Dosent seem to working. Am I doing anything wrong here.

Re: How to calculate difference between 2 time fields.

sunny_talwar — Thu, 02 Nov 2017 23:36:22 GMT

Try this

If(call_start_time_24 - web_end_time <= MakeTime(0, 15) ,'Online prior to call - web session started and ended before the call - 15 mins')

Re: How to calculate difference between 2 time fields.

Anonymous — Fri, 03 Nov 2017 00:13:32 GMT

I tried I am still getting null results. I thought there might be something wrong with my column format. So I tried loading like this in load script

time(Time#(TIME_24_HOUR_HHMMSS_FORMAT),'hh:mm:ss') as call_start_time_24

time(Time#(WEB_END_TIME),'hh:mm:ss') as web_end_time;

. Still getting null results. Tried changing minute number still getting null.

How can I see what exact data is being loaded into that column. If I want to check?

Re: How to calculate difference between 2 time fields.

Anonymous — Fri, 03 Nov 2017 00:26:09 GMT

I just checked I am not getting any values in that column Why would that be. An I doing anything wrong while loading .

The columns contains data in varchar format in my source like this. I am importing these columns with time#() function and not getting any values in those columns

Same with other column :

How to import these column in time format and compare.

Re: How to calculate difference between 2 time fields.

sunny_talwar — Fri, 03 Nov 2017 08:18:54 GMT

Try this

If(Time#(call_start_time_24, 'hh:mm:ss') - Time#(web_end_time, 'hh:mm:ss') <= MakeTime(0, 15) ,'Online prior to call - web session started and ended before the call - 15 mins')

Re: How to calculate difference between 2 time fields.

vcanale — Fri, 03 Nov 2017 12:38:45 GMT

try
=if(Interval(Time(call_start_time_24, 'HH:mm:ss') - Time(web_end_time, 'HH:mm:ss'))<='00:15:00', 'Online prior to call - web session started and ended before the call - 15 mins')

Re: How to calculate difference between 2 time fields.

Anonymous — Fri, 03 Nov 2017 18:06:47 GMT

Thanks that worked.