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.
Attached is a workflow with a java snippet node that performs the calculation.
I’ve tried to make it as usable as possible for people who don’t know how to write Java, and so it doesn’t actually rely upon the variable names for the incoming columns, per se (tech: it picks them up via Java Reflection) - they must just be the first two columns and added to the node.
So if you attach the Java Snippet node to input from a different node, or the same node but whose XLSX file you read has different column names, just make sure the data columns are the first two columns and then remove and re-add the columns as seen in the attached GIF (which isn’t attached because the forum software wasn’t having it - see here: http://theori.st/take_away/knime/pavan_node.gif )
I have followed the steps mentioned in the .gif, received the following errors as mentioned in the below screen shots. I am just guessing some setting related to localdatetime, not sure how to fix it. But i feel once this works it would be a great achievement.
Oh how weird… your nodes / version of KNIME / something … create Java types of LocalDate whereas for me, i get columns of Java types LocalDateTime (and so i also get the import statement for that class.) Are you reading in the exact same XLSX file that you uploaded to this thread?
This will sound like a pain in the ass, but everywhere you see LocalDateTime in that Java Snippet code, change it to LocalDate - which should be lines 36, 37, 44, 49, and 67.
Then on line 69, change it to:
out_totalcalendardays = new Long(ChronoUnit.DAYS.between(dates, dates));