Create Last working day of previous month in new column and calculate networkdays between two days in another new column

Hi KNIME Team,

I have the below requirement:
There are 40 thousand plus rows for due date (date range could be of any date, month and year) column which may increase or decrease month on month.

  1. I need to create a new date column that should populate last working day of the previous month (Suppose if i am running the workflow in Jan’19, it should provide me the last working day of Dec’18) for all the 40+ rows.

  2. Find the networkdays between last working day of the previous month and due date columns.

I have checked the similar topic in the forum with the workflow as shown in the below screen shot, but was not successful in getting the desired output.

Could someone assist on this please.

Regards,
Pavan.

Hi,

Would you please provide an example table of your input and desired output?

Hi armingrudd,

Here is the data table and the desired output attached in excel for your reference.
I have used some formulas to get the output in excel and want to achieve the same in KNIME.

Regards,
Pavan.

Hi Pavan,

Here I have a workflow which seems to be your solution:

The top flow creates current date (First day of the current month actually - just like your example)
The Main flow in the middle reads data, converts date column from string to Date&Time and adds current date. Then the holidays are joined to the table and and the “Column Expressions” checks whether the holiday is between due date and current date. Then the time difference (in days) between current date and due date is calculated (holidays are subtracted). The “Rule Engine” node labels the rows (0-30 days, 31-60 days,…) and finally pivoting and calculating the grand totals.

Please check the workflow and let me know if it works as expected for you:
Networkdays.knwf (101.0 KB)

Best,
Armin

1 Like

Hi Armin,

First of all a very big thank you for your helping hand and assistance. I am still in process of applying the workflow shared by you.

In the meanwhile i would like to check (open to all), the best or optimized way of such complex calculations through ETL process, as i feel that it is easy to do these in excel or database rather than KNIME.

This is just my opinion, more over i just have basic knowledge in KNIME (beginner).

Regards,
Pavan.