Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I'm kinda stuck with this problem which is like the last step to wrap up the business logic for my app.
I have to calculate time difference for a Datetime column for successive dates. For example
RequestID | Status | CreationDate | Difference |
1 | Originator | 9/5/2018 12:18 | - |
1 | Processor | 9/10/2018 12:20 | Diff |
1 | Validation | 9/10/2018 17:20 | Diff |
1 | Closed | 9/12/2018 9:20 | Diff |
Now when I take the difference for first and second date, I'll get a weekend(sat and sun) i.e. 48 hours i need to deduct from the total difference. Now this is just one example, there can be n number of weekends or holidays in between . Can anyone help me write the logic where I can get the exact number of weekends/holidays in between two successive dates in the column then I can just multiply it with 24 to get total hours to deduct.
I tried to use NetworkDays() function but it didn't work out as I wanted it to or may be I could not use it properly to get the desired outcome.
Any help or suggestions will be hugely appreciated. I need to get this done to get over the line as this is quite obviously a blocker for me.
Thanks in advance.
P.S. I'm working in Qlik Sense but I guess there won't be any difference in logic in QlikView.
Pranav
Hi Pranav, I needed to change the dates and the formats a bit. I changed the data so that multiple weekends are inlcuded. I created a date from and date till for every requestid with the previous function. Then I filled the dates between and flagged them when they are Saturday or Sunday. Then I summed the flags per requestID
Maybe you need a little adjustments, But you can try this script.
temp01:
LOAD
RequestID,
Status,
DATE(CreationDate) AS CreationDate,
DATE(IF(RequestID=PREVIOUS(RequestID),PREVIOUS(CreationDate),CreationDate)) AS Prev_CreationDate
INLINE [
RequestID, Status, CreationDate
1, Originator, 05-09-2018 12:18:00
1, Processor, 06-09-2018 12:20:00
1, Validation, 12-09-2018 17:20:00
1, Closed, 18-09-2018 09:20:00
];
temp02:
NOCONCATENATE LOAD
RequestID,
Status,
CreationDate,
Prev_CreationDate,
DATE(Prev_CreationDate+(ITERNO()-1)) AS Date,
IF(MATCH(WEEKDAY(DATE(Prev_CreationDate+(ITERNO()-1))),'Sa','Su')>0,1) AS NoOfWeekendDays
RESIDENT temp01
WHILE Prev_CreationDate+(ITERNO()-1) <= CreationDate;
DROP TABLE temp01;
Final:
NOCONCATENATE LOAD
RequestID,
Status,
CreationDate,
SUM(NoOfWeekendDays) AS NoOfWeekendDays
RESIDENT temp02
GROUP BY RequestID, Status, CreationDate;
DROP TABLE temp02;