Chang a date based on todays date and value in second field

I’m starting to lean Knime and have started with a little project. In this I have unfortunately got stuck on one step. I want to change some date fields that shows a date in the past based on the value in a different field.
If the date is in the past and the text value is Processing the date should change to the last day in the current month.
If the date is in the past and the text value is Opportunity the date should be moved to the last day of the next month.
There are other rules but if I have an example or tips on these two I should be able to work out the others. I want to learn by seeing if I can succeed with the other rules :slight_smile:
image

Hi @Hasenstrauch , maybe others can find a simpler workaround for your case, but here’s my take on it:

The image below is the result;

changingdates2

Here’s the workflow:

Changing Dates.knwf (165.3 KB)

Sorry I couldn’t figure out how to produce a 3-column result. Here’s additional notes about my workflow:

  1. Other than the data you provided, my workflow also fits for cases where there are more items under the Opportunity category, either with different, or with similar (redundant) dates.

  2. The disadvantage of a two-column result is that there is no certain way you can refer back to which row originates from which.

  3. I have sorted the results to make it easier for you to look at how many items you’ve got to focus on for deadlines that are assigned:

i) after the current month (green in color)
ii) at the end of the current month (blue in color)
iii) prior to the current month (orange in color)

  1. The workflow makes an important assumption that the current month corresponds to the Processing category, based on the sample of data you provided. So, let’s say you have a new dataset produced next month (May 2022), you should change the Processing label accordingly.

I hope others can help you out if my workflow isn’t useful. Good luck!

1 Like

Updated.

I finally figured out how to incorporate the original dates as a third column.

Here’s the updated workflow:
Changing Dates 2.knwf (189.2 KB)

And here’s the result:
changingdates4

Note: The order of the rows is no longer similar to that of the raw data, though. But hope this is still helpful !

2 Likes

@badger101 Thank you so much for this. :+1: It is really appreciated. I will start to look at it on Monday so lets see how it goes for me. Just looking at the picture there are a lot of nodes I haven’t seen/used before so it might take me a while but it will be fun learning what they do and how you solved this. :smiley:

2 Likes

Okay, and I also have a third version of the workaround here:
Changing Dates 3.knwf (192.0 KB)

This is to accommodate for duplicate dates for either the Processing or the Opportunity category, if such duplicates exist in your real data.

Thanks again @badger101
My fist impression is that it is complicated, I didn’t realise it was such a hard problem.

I have had a bit of time to look at version 2 and can understand most individual nodes. Now I have to understand the bigger picture of the all the nodes together…

I thought one could filter by category (processing), then by dates in the past, then change dates (to end of current month), then remove filters and do again with next filer set (opportunity + past)?

Hi @Hasenstrauch , the solutions might be complicated, but did the final output give you what you needed? If not, you might want to clarify what your end goals are. (With illustrations, if possible)

@badger101 yes the solution is what is important and yours gives me what I need. There is no critic of your method intended, just my acknowledgement of my lack of understanding… realising how complicated this problem is when you dig into it.
I had simplified the use case to make it easier to understand and my challenge now is to intergrade your workflow into data with many other columns. I will try to do this over the next week or so and let you know how it goes for me. Thank you so much for your help.

@badger101 A final note as I have now competed my task :slight_smile: I used a lot of your workflow, parts I found in the forum and parts I invented myself! Thanks again for your help, I would not have got it done without your help and learning from your example.

2 Likes

Alright, glad to help!

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.