Difference beetweeb dates?

[qlikuser222]

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

[robert_mika]

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)

[swuehl]

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.

[ecolomer]

Probably your problem are date format for startdate and enddate

[qlikuser222]

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??

[swuehl]

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

[ehilsinger]

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;

[maxgro]

try the attachment

[swuehl · Accepted solution]

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