topic Re: Difference beetweeb dates? in QlikView

Difference beetweeb dates?

qlikuser222 — Thu, 24 Mar 2016 20:38:41 GMT

Hi,

I have a requirement in which i need to find out difference between two dates excluding weekends.

Can any one please help me?

Please find attached which has raw data.

Thanks

Re: Difference beetweeb dates?

robert_mika — Thu, 24 Mar 2016 22:40:53 GMT

Interval((floor(timestamp(timestamp#(textbetween(enddate,'',' '),'MM/DD/YYYY'),'MM/DD/YYYY'))+

time(time#(textbetween(enddate,' ', ''),'h:mm:ss tt'),'h:mm:ss'))

-(floor(timestamp(timestamp#(textbetween(startdate,'',' '),'MM/DD/YYYY'),'MM/DD/YYYY'))+

time(time#(textbetween(startdate,' ', ''),'h:mm:ss tt'),'h:mm:ss')),'hh:mm:ss') as diff

Feeling Qlikngry?

How To /Missing Manual(25 articles)

Re: Difference beetweeb dates?

swuehl — Thu, 24 Mar 2016 22:42:17 GMT

Just use this statement to define your timestamp format:

SET TimestampFormat = 'M/D/YYYY h:mm:ss TT';

Note the extra space between date and time part.

Re: Difference beetweeb dates?

ecolomer — Thu, 24 Mar 2016 22:53:11 GMT

Probably your problem are date format for startdate and enddate

Re: Difference beetweeb dates?

qlikuser222 — Fri, 25 Mar 2016 16:27:57 GMT

Hi,

Thanks for helpfull soulutions.

I am still unable to get the expected results.

I dont understand why id 202 is repeating 4 times, where as in script its only mentioned twice.

can anyone please explain where it went wrong??

Re: Difference beetweeb dates?

swuehl — Fri, 25 Mar 2016 17:32:58 GMT

That's because the key between the two tables is only ID (Name has been renamed to NAME).

The Difference table still holds two records for ID 202, hence since the Name key is missing, you get both differences per ID.

Create a primary key made from ID and Name in both tables or just use e.g. a preceding load with the diff code in the first load to overcome this issue.

Regarding your original request, I believe the code from your last answer does not exclude weekends.

And do you need the difference between timestamps or just the date difference? If latter is sufficient, you should be able to get what you want using NetWorkDays() function (that's what you also used in your sample QVW).

Just take care to interprete the timestamps correctly when reading in.

Regards,

Stefan

Re: Difference beetweeb dates?

ehilsinger — Fri, 25 Mar 2016 18:38:27 GMT

Try the below script.

You have two spaces between the date and time. I also used date# first.

Test:

load * inline [

ID, Name, startdate, enddate

202, REM, 1/27/2016 2:34:32 PM,1/29/2016 4:47:57 PM

202, WAQ, 1/27/2016 2:34:33 PM, 2/1/2016 3:58:17 PM

303, XXX, 1/27/2016 2:34:35 PM, 2/3/2016 2:35:00 PM

303,YYY,2/23/2016 7:20:56 PM,3/24/2016 2:08:02 PM

404,XXX,1/5/2016 4:19:58 PM,1/6/2016 10:15:46 AM];

Difference:

NoConcatenate

load ID,

Name as NAME,

startdate,

enddate,

Date (date#(startdate,'MM/DD/YYYY hh:mm:ss TT')) as newstartdate,

Date(date#(enddate,'MM/DD/YYYY hh:mm:ss TT')) as newenddate,

NetWorkDays(Date (date#(startdate,'MM/DD/YYYY hh:mm:ss TT')), Date(date#(enddate,'MM/DD/YYYY hh:mm:ss TT'))) as diff

Resident Test;

DROP Table Test;

Re: Difference beetweeb dates?

maxgro — Fri, 25 Mar 2016 18:39:28 GMT

try the attachment

Re: Difference beetweeb dates?

swuehl — Sat, 26 Mar 2016 11:07:28 GMT

And if you want to calculate duration between timestamps excluding weekends, have a look at

Calculate hours between two Date/Time strings

Then your script could look like

SET TimestampFormat='M/D/YYYY  h:mm:ss[.fff] TT';

Test:
load *, 
NetWorkDays(date(startdate,'M/D/YYYY h:mm:ss'), date(enddate,'M/D/YYYY h:mm:ss')) as diffNetWorkDays,
Recno() as RecID
 inline [
ID, Name, startdate, enddate
202, REM, 1/27/2016  2:34:32 PM,1/29/2016  4:47:57 PM
202, WAQ, 1/27/2016  2:34:33 PM, 2/1/2016  3:58:17 PM
303, XXX, 1/27/2016  2:34:35 PM, 2/3/2016  2:35:00 PM
303,YYY,2/23/2016  7:20:56 PM,3/24/2016  2:08:02 PM
404,XXX,1/5/2016  4:19:58 PM,1/6/2016  10:15:46 AM];


Set vHol = '41130,41140'; // just for demo optional holidays

TMP:
LOAD  RecID,
daystart(startdate)+iterno()-1 as Date,
if(iterno()=1, frac(startdate), 0) as Start,
if(daystart(startdate)+iterno()-1=daystart(enddate), frac(enddate),1) as End
Resident Test
while daystart(enddate) >= daystart(startdate)+iterno()-1;

left join (Test) LOAD
RecID,
sum(End-Start) as DurationHours
Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by RecID;

drop table TMP;

ID	Name	startdate	enddate	diffNetWorkDays	Interval(Sum(DurationHours),'h:mm:ss')
					784:20:28
202	REM	1/27/2016 2:34:32 PM	1/29/2016 4:47:57 PM	3	50:13:25
202	WAQ	1/27/2016 2:34:33 PM	2/1/2016 3:58:17 PM	4	73:23:44
303	XXX	1/27/2016 2:34:35 PM	2/3/2016 2:35:00 PM	6	120:00:25
303	YYY	2/23/2016 7:20:56 PM	3/24/2016 2:08:02 PM	23	522:47:06
404	XXX	1/5/2016 4:19:58 PM	1/6/2016 10:15:46 AM	2	17:55:48

Hope this helps,

Stefan