For the last one Iris have provide the solution as “I would first make a list of all days between the two days. Than extract the day of week, filter all Saturdays and Sundays and finally count the remaining using a groupby node.”
I am not sure how do i apply that to my requirement.
you want todo the calcultation in excel or in knime? if excel you can look for the date&time formulas: I have italian version and the formula is =GIORNI.LAVORATIVI.TOT(cell#1;cell#2)
I hope it help
There is an R library that can do business day calculations in various forms. You might have to adjust how the package handles the start and end day and what to do if the start or end day is a holiday. Also the actual holidays seem to be derived from a localised list - you could also adapt that or set your own list (I was not able to turn the list off completely). And also for your negative examples there seem to be differences in the calculation method.
I did find another solution with Python in one of the above links. since both of these solutions has dependency on other applications (though they are open source), i thought it would be better to do in KNIME itself.
This sounds easier than it looks like! But still not too bad. Note that this is not efficient way cause 1 row is one loop iteration. I only read 10.000 rows in attached workflow. For that execution is around 5 minutes.
I have tried the workflow shared by you, this seems to be some complicated for me (its just my opinion). However as mentioned by you, it is not efficient way to get the required output. I think it would be wise to use other suggestions that we have like java, R, Python, etc.
I don’t know java, i did tried with the workflow shared by @mlauber71 and i have the below error screen shots. I did check in R application and library (bizdays) is available.
Sorry - i’m not clear on what “aging” is supposed to mean - i see the value, but it’s still not clear how it relates what i am calculating. I’ve augmented my workflow to also generate a column that is “weekdays ignoring holidays” - so basically, if it is not a Saturday or Sunday then it is counted.
Even with this new column, the ‘aging’ column is sometimes (this +/- 1), sometimes (this), sometimes (this +/- 2), … very rarely 3 or 4 off… very much not clear. (I’ve also now made a delta column).
Maybe if you could explain what “aging” is, we could better solve this; if this value does incorporate locale-specific holidays, then you would need to encode the date logic for those yourself in the appropriate method in the Java Snippet node. pavan’s working days.knwf (7.7 KB)
@quaeler: The aging column is the actual aging calculated in excel for reference. The output from the KNIME workflow should match to it. The aging can be +/- also. Right now i am in a situation where i do not have any knowledge on java and not able to apply to the workflow.
@mlauber71: i have very very basic knowledge on python and R, i am just trying with both of the programs and see how that can be integrated with KNIME from past 2 days. could you suggest on how to run either R or Python general snippets in KNIME.
I can give a few links about Knime and R. I would suggest you start with the installation and then use your question here and then move on. For python and KNIME I also have a few links. But my impression is the setup with KNIME is not so straightforward. I might have a second installment about that.
Installation of R
Video tutorial KNIME and R
Basic usage of R and KNIME
The collection of R nodes
(Below every description there are links to sample workflows)
When you say “the actual aging calculated in excel for reference,” that doesn’t explain how to get a value for “aging”, and if i open your .XLSX in Excel, i see that those columns values are simply numbers and not a formula.
Without an explanation of how one calculates “aging,” you will have a definite problem writing code or cobbling together nodes to, well, calculate aging.