I have 2 Date columns which need to be manipulated based on non-working days. If a date is working day - keep it, else shift the date to next working day.
For example: If Day 1 if Saturday I have to shift it by 2 days to Monday. But if Monday was a bank holiday then shift it by 3 to Tuesday…
I have a main data table and Calendar table which defines if each day is a working day or not.
Any help is greatly appreciated.
Nice to see you back!
Let’s check if I have understood your issue well.
You want to have a column starting from 1 which represent the working day on calendar where the counting halts on non working days?
If yes, how about filtering the holidays first and then using a Counter Generation node?
Then you can join the initial table (top port) with this new table (bottom port) on date and use left join.
This way you have a complete calendar with a column counting working days.
I created a workflow data_manipulation_based_on_non_working_day.knwf (70.9 KB)
based on the tip from @armingrudd . When you have your calender there is column called ok_day that indicates if (ok_day=0) the “amount” should be shifted to the first ok_day=1. The result will look like this
(I created the ok_day 1/0, for testing purposes, not using a calender, so Sunday maybe an ok_day(=1)
Thank you both for your replies. I am working on a KPI which looks at Time to initial response and need to find the difference between 2 Date-time columns. But want to take out the duration of non-working days/hours if it falls between the 2 dates.
Apologies for not being clear enough.
It is always a good idea to provide a sample input data and desired output as an example.
Actually in most cases, if a good input and desired output is provided, I don’t even need any explanation and will provide the solution.
So if this is possible for you, please provide an example of your input and desired output.
thank you for your quick response.
Attaching a sample Excel sheet with 2 examples of what the expected time difference is (Column F).Sample Times-forum.xlsx (11.0 KB)
Also attaching a CalenderCalender-Forum.xlsx (40.3 KB)
So you want to subtract a whole day for weekends and holidays and 7.5 hours for each Friday and Monday, right?
OR any working day as well.
If a work was submitted 8:30 am Monday and Responded on Tuesday 10:00am, the hours to respond should be 10 hours and 30 minuted - not 25 hours and 30 minutes.
Thank you for the explanation.
I will get back to you, if @HansS doesn’t act on this faster.
I have built a workflow which does what you want. But I think it can be optimized. Any suggestions for optimization is appreciated @HansS.
Work Time Calculation.knwf (184.4 KB)
Thank you very much for this. I will try to understand this and get back.
Please could you explain why the 1000 as the maximum number of iteration in the Loop end node? I would like to understand the recursive loops better to adapt your workflow to my needs. At the moment the iterations take a bit too long with the sample data.
Thank you for your time to help
That’s the max number of iterations of the loop but I used a flow variable to close the loop. Date variable which becomes true when the output table from Row Filter (Node 16) is empty. Take a look at the configurations of the next Table Row to Variable node. It’s better to use a Extract Table Dimension node after Date&Time-based Row Filter (Node 11) and use number of rows plus 1 for max number of iterations.
I wonder if it is possible to run the iterations for Calender dates between Submit Date and Process Date. That would reduce the number of iterations drastically. At the moment the workflow is taking a long time to run as its looking through all of the Dates after the row filter (node 11).
The Calendar is already filtered to have the dates between the first submit date and the last process date before the loop.
I mean for each row in the main data, if the starting date and end date could be defined when the calendar data is being looped through to get the number of working days.
Example: For Submit Date- 22 -12-2018 and Process Date - 02 -01-2019, The calender looping will run between those 2 dates to count the number of working days between.
The calendar dates are filtered to contain dates from the first submit date to the last process date in the main table AND it is filtered to include just the working days. All before the loop.
The loop then checks the remaining dates from the calendar on the main table to count the working days.
Then how about the rest of the main table?
Anyway you have to loop all the dates between your first submit date and the last process date in the main tabel.
Sorry to break into your discussion but @armingrudd invited me in his post above Date manipulation based on Non-working day to do any suggestions for optimization. Well it was not so easy , instead of a recursive loop I used a chunk loop and that makes this workflow much faster (but needed more nodes).
d_m_based_on_non_working_day.knwf (70.0 KB)
Nice job @HansS.
It just came to my mind that if the main dataset is larger then your solution takes more time since it loops over the main table instances and if the we have fewer records then it does the task much faster.
Still in both cases my solution is slower because of the Column Expressions.
Here I have a suggestion for you @HansS:
You take my workflow and follow the same approach I have used and just optimize it to reduce the execution time and I take yours to do some modification keeping the same approach you have used.
Then we can create a master workflow which uses any solution that fits better to the case.
What do you think?