topic Re: Find time difference in QlikView

Find time difference

jyothish8807 — Thu, 26 Dec 2013 04:41:16 GMT

Hi,

I have two fields : 1) date and time received : 12/16/2013 07:23 am

2) date and time sent : 12/17/2013 05:00 pm

No how can i find the total hours between date and time sent and date and time received.

Thanks & Regards

Jyothish KC

Re: Find time difference

jagan — Thu, 26 Dec 2013 05:26:26 GMT

Hi,

Try using Interval()

=Interval(TimeSent - TimeReceived, 'H')

Example:

=IntervaL(Timestamp#('12/17/2013 05:00 pm', 'MM/DD/YYYY HH:mm TT') - Timestamp#('12/16/2013 07:23 AM', 'MM/DD/YYYY HH:mm TT'), 'H')

Output : 33

Regards,

Jagan.

Re: Find time difference

jyothish8807 — Thu, 26 Dec 2013 11:30:49 GMT

Thanks for the reply, but still i am getting a difference of 23:00 for intervals more than 1 day.

Eg: 12/15/2013 01:00 PM ( date received)

12/17/2013 4:00 PM ( date Sent)

Re: Find time difference

VishalWaghole — Thu, 26 Dec 2013 11:52:56 GMT

Hi jyothish,

Its returning 51hr, By using Jagan's logic.

- Regards,

Vishal Waghole.

Re: Find time difference

Thu, 26 Dec 2013 11:55:41 GMT

Hi Jyothish,

Try this script,

Diff:

LOAD *Inline [

Received ,Sent

12-16-2013 07:23,12-17-2013 05:00

];

Diff01:

LOAD Timestamp#(Received,'MM-DD-YYYY HH:mm') as Received_Conv, Timestamp#(Sent,'MM-DD-YYYY HH:mm') as Sent_Conv Resident Diff;

Diff02:

LOAD Interval(Sent_Conv-Received_Conv,'HH') as Diffrence Resident Diff01;

Re: Find time difference

Thu, 26 Dec 2013 12:19:45 GMT

Hi,

You can try hour(time((date sent),'hh:mm:ss'))-(time(date recieved),'hh:mm:ss').

Regards,

Piyush

Re: Find time difference

jagan — Fri, 27 Dec 2013 02:17:27 GMT

Hi,

Check this, it is working and it returns 51 as output

=IntervaL(Timestamp#('12/17/2013 4:00 PM', 'MM/DD/YYYY HH:mm TT') - Timestamp#('12/15/2013 01:00 PM', 'MM/DD/YYYY HH:mm TT'), 'H')

Regards,

Jagan.

Re: Find time difference

jyothish8807 — Fri, 27 Dec 2013 06:35:47 GMT

Hi Jagan,

Actually i have two column " Date" and other is " Received Time". So i am combing both fields in Excel and creating "Date and time received" field.Is there any way to create this field in QV instead of excel. I think because of this i am facing issue.

Re: Find time difference

jagan — Fri, 27 Dec 2013 06:40:56 GMT

Hi,

Can you attach some raw sample data in Excel and come up with an example what actually you need?

Regards,

Jagan.

Re: Find time difference

jyothish8807 — Fri, 27 Dec 2013 06:50:07 GMT

In My Case Elapsed Time is not coming beyond 23 .

Re: Find time difference

jyothish8807 — Fri, 27 Dec 2013 06:55:36 GMT

Sure Jagan,

This a sample of my excel sheet.

Name	Date Received	Time Received	Date sent	Time Sent
a	17-Dec-13	3:44 AM	18-Dec-13	2:39 PM
b	17-Dec-13	3:44 AM	17-Dec-13	3:25 PM
c	17-Dec-13	3:44 AM	23-Dec-13	3:25 PM

Now i am trying to find out the time duration between both.

Re: Find time difference

jagan — Fri, 27 Dec 2013 07:10:35 GMT

Hi,

Try this script

LOAD *,

IntervaL(Timestamp#([Date sent] & ' '& [Time Sent], 'D-MMM-YY HH:mm TT') - Timestamp#([Date Received] & ' '& [Time Received], 'D-MMM-YY HH:mm TT'), 'H') AS Diff

INLINE [

Name,Date Received, Time Received, Date sent, Time Sent

a, 17-Dec-13, 3:44 AM, 18-Dec-13, 2:39 PM

b, 17-Dec-13, 3:44 AM, 17-Dec-13, 3:25 PM

c, 17-Dec-13, 3:44 AM, 23-Dec-13, 3:25 PM];

Regards,

Jagan.