Creating new rows based on difference between 2 date fields

KNIME Analytics Platform
1

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
2

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 

3

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

5

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.

Untitled
Untitled868×196 10.6 KB

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
7

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
9

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
10

Hi there!

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

Br,
Ivan

11

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

1 Like
12

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

4 Likes
13

Many thanks, I really appreciate that

1 Like
14

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

1 Like