Replace Latest Date of the month with First Day of the same month

KNIME Analytics Platform
1

Hi, I want to convert the latest date of every month into the first date of that particular month.

Can someone help me here please?

For Example:
Date Conversion

Pasting the data here for your ease.

Current Date Column Updated Date Column
08-01-22 01-01-22
10-01-22 10-01-22
12-01-22 12-01-22
02-02-22 01-02-22
08-02-22 08-02-22
10-02-22 10-02-22
15-02-22 15-02-22
07-03-22 01-03-22
08-03-22 08-03-22
09-03-22 09-03-22
12-03-22 12-03-22
14-03-22 14-03-22
15-03-22 15-03-22
17-03-22 17-03-22
06-04-22 01-04-22
07-04-22 07-04-22
08-04-22 08-04-22
09-04-22 09-04-22
11-04-22 11-04-22
12-04-22 12-04-22
14-04-22 14-04-22
16-04-22 16-04-22

Regards,
Samir

2

Hi @Samir_Nagure

Please find below a workflow with a possible solution to your question:

image
image1778×253 85.8 KB

image
image514×587 92.1 KB

This could be solved in a simpler way using only the -String Manipulation- and the -Lag Column- nodes. However, I’m suggesting this solution here to illustrate how to convert strings into dates and vice-versa too.

Hope it helps.

Best
Ael

3

@aworker awesome solution! Please remind me the string to datetime node requires a specific format right? (E.g. leading zeros, or 4 digits,…)
Sometimes I am a little confused when KNIME is not picking up the date and throws an error. Maybe I should use the node more often ^^
thanks

4

Hi @Daniel_Weikert !

Thanks for your message and compliments :grinning: !

Indeed but the node has a very nice feature in the configuration menu which most of the time manages to guess the right format. Having said this, it not always succeeds and a bit of help beforehand with a -String Manipulator- node might facilitate a lot its success.

I am not a specialist of “Date & Time” conversion (only a DIYer haha ha). However @takbb did a tremendous and remarkable work on implementing a component which understands most of Data&Time formats:

Thanks a lot @takbb ! Hope it helps :wink:

I take this opportunity to wish you @Daniel_Weikert, @Samir_Nagure, @takbb and to all the Knime Forum Community a very Happy New Year :grinning: !

Best
Ael

5

I was just confused because the node did not want to pick up my 2 digit year so I switched to python node instead ^^

Thanks a lot @aworker to you and all the other valuable contributers as well

6

I believe the node is not happy with a 2 digits year which I can understand since it is prone to ambiguity and miss-confusion with months and days. I always rather prefer to convert the year to a 4 digits format.

Best
Ael

7

Hi Ael,

Thanks a lot for your help. It resolved my issue.

Thank you again and Wish You All a Very Happy New Year…

Regards,
Samir