Hello,
There is a challenge when I am dealing with date format information. It would be highly appreciated if anyone can show me a way to solve this challenge.
Challenge description
The original date format is not continually in days, namely the column of date is showed as these 2 columns: start date:22-01-01, End date: 2022-9-17
My target is that: can I insert the missed date range rows e.g. Start date" 2022-09-18, End date: 2022-12-31, is there any node in Knime platform that can automatically find the gap range and then insert the missing date range of the whole calendar year.
For example, I have these data, for these 2 people:
Some periods of the whole 2022 calendar year are missing.
I want write a KNIME flow that automaticly add the missing periods rows, like this:(the red 2 rows are the new added missing periods).
I only found solutions for adding missing dates so far using “Ctreat Date&Tiime Range” node. However my situation is adding missing periods.
Just consider 2022-01-01 to 2022-12-31 as the whole calendar year.
If one person’s periods don’t consititute to the whole year (2022-01-01 to 2022-12-31), then add missing periods accordingly at the right positions. For example:
We add additional one row for the missing days between 2022-09-18 to 2022-09-19. The new row should be after the first row and before the third row, so the whole periods in 2022 for this person are continuously.
@kellyhui OK I do understand, do you have a dataset that you can upload? Also I understand there can be more then one missing row. Will there be several years or just one?
@mlauber71
Thank you so much about this python extension help. It worked!
As I am a totally beginner, I don’t know understand the first line of code “import knime.scripting.io as knio”? Is this loading the data I have? and where can I find the name of this input file?
Can I also please ask you one additional question after adding these 2 missing rows?
I have other columns for each person except start date and end date. For the new adding row, I want to copy other columns from the last period for this person. For example:(the red column is the new added row and other columns of this new row are the copies from the row before). I updated new excel input file with 2 other columns.
It would be great if you can share your KNIME Python Script code and give me some hints about how to search chatgpt wisely to get the code I want. Test file.xlsx (10.3 KB)
@kellyhui you can read about python and knime and the use of ChatGPT in the two articles I linked.
The import … would activate the KNIME python module called “knio”. This is how Python handles its modules. From my perspective it will make much sense to learn just enough python to use such small snippets to extend one’s abilities.
You can load the full example from the entry or the KNIME hub and try yourself. You can import the .knar file in your own knime version.
If you want to fill columns with values from above you can usev the missing value node:
