Creating new rows based on difference between 2 date fields

Hi, I am quite new to Knime, so I am hoping that someone more expericence will be able to help me with this problem.

I have a table with various columns, 4 of which are person, activity, start date and end date. 

I would like to be able to create new rows based on the difference between start date and end date. I understand about de-pivoting, but my problem is how to create the additional dates in a range as values in the column.

Example: 

Person /Activity/Start_Date/End_Date

John/Activity1/2017-01-01/2017-01-03

 

Desired outcome

Person/Activity/Date

John/Activity1/2017-01-01

John/Activity1/2017-01-02

John/Activity1/2017-01-03

I hope this makes sense and  thanks in advance for any help with this.

1 Like

Hi,

you need some steps for this:

1. calculate the number of days between using the time difference node

2. multiply the number of rows using a one to many node.

3. add a counter using the counter node

4. shift the start date (Shift Date) using the number of days as produced by the counter.

Hope this helps, best, Iris 

Hi Iris,

Thank you for your reply and apologies but I have only just seen it. I will t be interested to try this out, as in the meantime, I used a java snippet node (with some help!), which worked.

I did get as far as your steps 1 and 2 before I asked the question, but then got stuck!

 

Thank you again

Hi there!

Maybe I can help with this matter. I have done it using Create Date&Time Range which is controlled with flow variables produced by Table Row to Variable Loop Start node.

Here is workflow. Take a look and if any questions feel free to ask.

2019_05_02_From_One_Row_To_Many.knwf (19.2 KB)

Br,
Ivan

4 Likes

Hi there!

The idea is same pretty much. In both Java and Python you need to find function that creates date range based on your start and end date followed by a bit of data manipulation to get desired output…

Why do you want to use Java or Python and not KNIME nodes?

Br,
Ivan

2 Likes

Hi,

You can use modify time node to remove time stamp from date time and try create date time range node. hope this helps.

Ivan please correct me if i am wrong.

Regards,
Pavan.

2 Likes

Hi there!

Correct. Modify Time node can be used to remove time from Date&Time column type.

Br,
Ivan

Excelent, this helped me a lot to cover one top functionality from Alteryx!

1 Like

Hello @Sgomezba,

glad to hear that. Created modified version to replace deprecated node and add loopless version.
2019_05_02_From_One_Row_To_Many_Loopless.knwf (46.2 KB)

Br,
Ivan

3 Likes

Many thanks, I really appreciate that

1 Like

thanks, interesting to see that it works with dates formatted as string as well
br

1 Like