topic Re: Date Time difference in QlikView

Date Time difference

Tue, 29 Jul 2014 18:41:46 GMT

I'm trying to calculate the difference between 2 date/time stamps, the issue I'm having is that I only want to do it where the 2 dates are the same.

I've tried using the ceil/floor functions within an if statement, but it's not working.

Something like: if(Date1 = Date2, max(datetime2) - min(datetime1), 0) as Loadtime

Any ideas on this?

Re: Date Time difference

joshabbott — Tue, 29 Jul 2014 19:00:25 GMT

Try this

=if(month(date1) = month(date2) and day(date1) = day(date2) and year(date1) = year(date2), date1- date2,0)

Re: Date Time difference

Tue, 29 Jul 2014 20:03:29 GMT

That didn't seem to work either.

These are load time for tables. Different tables can load at the same time & therefore I cannot simply subtract the time periods. I need the Minimum Start Time for the day & the Maximum End Time for the day to determine how long it took to run as a whole process.

Re: Date Time difference

joshabbott — Tue, 29 Jul 2014 20:21:46 GMT

I'm not understanding. What are the value's in your date fields? The code above that I added checks if the date is the same down to the day, but still possibly different based on the hours/minutes/seconds. Could you write up a quick little sample of what date1, date2, and your result based on those values being?

Re: Date Time difference

Tue, 29 Jul 2014 20:29:30 GMT

Example:

Table Start Date/Time End Date/Time

1 4/1/14 12:45:00 4/1/14 12:46:00

2 4/1/14 12:45:00 4/1/14 12:49:00

3 4/1/14 12:48:00 4/1/14 12:50:00

4 4/1/14 12:49:00 4/1/14 12:51:00

5 4/1/14 12:51:00 4/1/14 12:56:00

1 4/2/14 12:45:00 4/2/14 12:49:00

2 4/2/14 12:45:00 4/2/14 12:53:00

3 4/2/14 12:49:00 4/2/14 12:51:00

4 4/2/14 12:53:00 4/2/14 12:56:00

5 4/2/14 12:58:00 4/2/14 01:12:00

I would like to show that for 4/1/14 the time to load the tables were 12:56:00 - 12:45:00 for a total of 12 minutes for 4/1.

for 4/2, I'd like to show that the time to load was 01:12:00 - 12:45:00 for a total of 27 minutes. So that I can graph the daily load times by date.

I hope that helps explain it better.

Re: Date Time difference

Anonymous — Tue, 29 Jul 2014 21:48:30 GMT

Hello

You can calculate the difference in minutes or seconds and do the conversions for hours, days, etc ... For example:

=Minute((now() - today()))

Re: Re: Date Time difference

rwunderlich — Tue, 29 Jul 2014 22:28:35 GMT

How about:

Detail:

LOAD

date(floor([Start Date/Time])) as Date

;

LOAD

Table,

Timestamp(Timestamp#([Start Date/Time],'MM/DD/YY hh:mm:ss tt')) as [Start Date/Time],

Timestamp(Timestamp#([End Date/Time],'MM/DD/YY hh:mm:ss tt')) as [End Date/Time]

INLINE [

Table, Start Date/Time, End Date/Time

1, 4/1/14 12:45:00 PM, 4/1/14 12:46:00 PM

2, 4/1/14 12:45:00 PM, 4/1/14 12:49:00 PM

3, 4/1/14 12:48:00 PM, 4/1/14 12:50:00 PM

4, 4/1/14 12:49:00 PM, 4/1/14 12:51:00 PM

5, 4/1/14 12:51:00 PM, 4/1/14 12:56:00 PM

1, 4/2/14 12:45:00 PM, 4/2/14 12:49:00 PM

2, 4/2/14 12:45:00 PM, 4/2/14 12:53:00 PM

3, 4/2/14 12:49:00 PM, 4/2/14 12:51:00 PM

4, 4/2/14 12:53:00 PM, 4/2/14 12:56:00 PM

5, 4/2/14 12:58:00 PM, 4/2/14 01:12:00 PM

]

;

Summary:

LOAD

Date,

interval(max([End Date/Time]) - min([Start Date/Time]), 'hh:mm:ss') as [Load Duration]

RESIDENT Detail

GROUP BY Date

;

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Date Time difference

Fri, 01 Aug 2014 19:05:47 GMT

Thank you Rob! I was able to get that to work!