Working Days/Network Days between two dates

[mlauber71]

Maybe you could share the Python solution for further reference. I thought about a pure KNIME solution but was not successful as of now.

[quaeler]

Were it not for the “holidays” which seems to require importing calendrical information, Java 8 has a java.time package which makes this is pretty straight-shot using the Java Snippet node.

[pawanmtm]

@mlauber71: It is available in the 1st link that provided above.
For quick reference, i am updating the link here.
https://het.as.utexas.edu/HET/Software/Numpy/reference/generated/numpy.busday_count.html

@quaeler: Thanks and appreciate your suggestion, i looking to do it with KNIME itself, unless it is highly impossible/time consuming/performance issue.

Assistance from anyone is welcome.

Regards,
Pavan.

[mlauber71]

In this thread there is a KNIME workflow to extract a list of holidays for different countries from a webpage

Dividing and splitting rows

I constructed a workflow that illustrates how you could do it. Basically you first do it like in the example above you count down from the original TargetDay the you expand the possible TargetDays by factor 3 (so you will have enough days once you start eliminating some in-between) then you mark all days that are not suitable like holidays and weekends the remaining ones you just join back by an iteration number and voila you have the new TargetDays [19] the yellow marked columns state why …

And also there are is an example how to shift a target date depending on several factors. It is very much possible to adapt that approach.

Where I was not successful yet is how to construct a calendar from a start and an endpoint. Not sure if that would mean one has to use a somewhat dirty loop.

[quaeler]

Attached is a workflow which does it all in KNIME - it uses a pretty questionable, US-only, “is this a business day method” that i cribbed from GitHub (cited in the Java Snippet code.)

Apparently Ruby (i throw up in my mouth) has a gem that does this sort of thing really well ( https://github.com/holidays/holidays ) and there’s an online service ( https://holidayapi.com/ ) which does this really, really, well - but is for-pay.

At any rate, hope this helps.

pavan’s working days.knwf (7.5 KB)

ps. no assertions that this is well written Java code - just gets the job done.

[ipazin]

Hi there!

Here it is in KNIME and KNIME only

@pawanmtm

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.”

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.

Take a look and if any question feel free to ask.

2019_04_04_Number_of_Working_Days_Between_Two_Dates.knwf (1.7 MB)

Br,
Ivan

[pawanmtm]

@ quaeler: i have tried this, the output data is not matching with the actual aging, not sure if am missing something. once again thanks for your assistance.

Regards,
Pavan

[pawanmtm]

Hi Ivan,

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.

Regards,
Pavan.

[quaeler]

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)

[mlauber71]

Could you check if you can run R snippets in general.

Then make sure you have the latest version of R set up and running following these hints:

Install R alongside KNIME on Windows and MacOS

Over the last months I have collected a few entries about the installation of R alongside KNIME that contain some maybe useful hints how to overcome certain obstacles while installing them. I want to bring them together here. (If you are looking for a basic guide about how to install R in the first place you might look here or here). A few maybe unnecessary remarks upfront. Yes it takes some effort to get KNIME and R up and running but in the end you will be rewarded with the whole world of pos…

[pawanmtm]

@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.

Regards,
Pavan.

[mlauber71]

@pawanmtm

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

Install R alongside KNIME on Windows and MacOS

Over the last months I have collected a few entries about the installation of R alongside KNIME that contain some maybe useful hints how to overcome certain obstacles while installing them. I want to bring them together here. (If you are looking for a basic guide about how to install R in the first place you might look here or here). A few maybe unnecessary remarks upfront. Yes it takes some effort to get KNIME and R up and running but in the end you will be rewarded with the whole world of pos…

Video tutorial KNIME and R

Microsoft R Integration in KNIME

Basic usage of R and KNIME

Basic R Usage – knime

The collection of R nodes

R — NodePit

(Below every description there are links to sample workflows)

[pawanmtm]

@mlauber71: Thanks for the information, i will spend some time to understand R & Python and see how they can be interpreted to KNIME.

Regards,
Pavan.

[quaeler]

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.

[pawanmtm]

here is the formula used to calculate the aging. i have re-attached the file for your reference.

Calculate Aging.xlsx (2.7 MB)

Regards,
Pavan.

[quaeler]

Ok - after reading the function definition at https://support.office.com/en-us/article/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7 and seeing that you’re not using a holiday specifying version of it, it was straightforward.

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 )

pavan’s working days.knwf (8.1 KB)

[pawanmtm]

Hi @quaeler

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.

Regards,
Pavan.

[quaeler]

What version of KNIME are you running?

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[0], dates[1]));

[pawanmtm]

Hi Quaeler,

I have changed as suggested by you, it worked. But there is 1 difference in the output, the aging is always as positive. Ideally if the start date is greater than end date, the aging should be in -ve. May i know where should i change the code for this.

Regards,
Pavan.

[quaeler]

Line 78 should then read (note <= instead of <)

if (firstColumn.compareTo(secondColumn) <= 0) {

and line 104 should then read

out_networkdays = new Long(weekdays * multiplier);

Also please do confirm what version of KNIME you are running and which, if either, of the attached XLSX files in this thread is the one you are reading in your workflow - it is very important to understand whether the difference in Java types is due to KNIME version, data version, or something else.