Create a loop for date&time shift

Hi guys

I need some help on looping a date&time shift node.
I have a date and I have to shift it before and after it by -5 and +5 (hypothetically, it can be also -10 to +7)
Without doing it manually, is there a loop that can do it automatically?

Ty
R

Hey @Reader91,

In this example I’m taking the “DateTime” column and shifting it up (and down) by 5 days to generate the other two columns, which sounds pretty close to what you’re describing.

date_time_shift.knwf (9.3 KB)

image

Cheers,

@sjporter

1 Like

Hi

I don’t need only the two limits but every date between the two (e.g. -5, -4, -3 and so on until the upper limit)

R

Hi @Reader91 , you mentioned date&time, so what units are you limits in (the -5 and +5)? In days? In hours? In weeks?

Since you mentioned that you “have a date and I have to shift it before and after it by -5 and +5”, and you mentioned that you want “every date between the two” limits, I am assuming that you are dealing with dates only, and that the units of the limits are in days.

I put something together for you based on the above assumption, and it looks like this:

Input data looks like this:
image

And results as follows:

And here’s the workflow: Get every dates between 2 limits.knwf (24.4 KB)

1 Like

I borrowed @bruno29a’s flow to save effort… :wink:

I know the original question was how to do it with a loop, but here is a way of doing it without a loop, but does require the One Row to Many node to be installed. (It is installable as an extension/from the hub). It uses more nodes, and isn’t necessarily better or worse, but just gives an alternative.

[edit: I’m not actually quite sure why my results differ slightly from the same test data. I need to investigate further! Thought it was to do with original date, but I now see it isn’t quite that]

Get every date between 2 limits without loop.knwf (23.3 KB)

1 Like

Hey @takbb , I think when when @Reader91 mentioned the loop, it was about creating the dates using a loop.

In the workflow I presented, it’s not exactly the case. The Create Date&Time Range node is creating the dates. The loop I am using was to read the input lines one by one so I could process them one by one.

Thinking about it now, I am not sure why I needed to do it this way lol :rofl:. There is indeed no need for a loop at all.

I just noticed that indeed, the original date is not included in my results, which was NOT intended, so it’s a bug on my part, and I know why it’s happening - it’s basically my NumOfDays which is doing the sum of days before and after, hence not including the original date.

Your results should be the expected results.

EDIT: Modified the Math Formula from:
abs($lower limit$) + $upper limit$
to
abs($lower limit$) + $upper limit$ + 1

And it works as expected.

I also edit what I said about the loop. Now I see why I needed the loop, it was to be able to convert the rows to variables in order to pass the variables to the Create Date&Time Range node. So, I think there is no going out of using loop if we use the Create Date&Time Range node.

Here’s the updated workflow to include the original date:
Get every dates between 2 limits.knwf (24.4 KB)

But as we always say, there is always more than 1 way to do things in Knime, and I am happy to see an alternative without loop. It’s always interesting to see what you can do as alternatives @takbb :slight_smile:

1 Like

Thanks for the updated comments @bruon29a. Very useful. I have been having a look at the Create Date&Time Range node, as I’d not really used it before.

It’s a shame there isn’t a dataflow version of this that works like the One Row to Many node, as I could see this would be really useful, and would remove the need for looping with it. One Row to Many does a good job, but I end up having to include so many more nodes just to ultimately create a new date column! There have been a few occasions now where I’ve wanted to expand a date range out within my existing data, and this would be ideal.

btw, I also “discovered” that if you are passing Start Date and Nr of Days as flow variables, then you can get away with not having to supply End Date, provided that you set the Ending Point Interval to “1D” (i.e. 1 day) on the config, instead of marking it as “End:”.

That way it generates daily durations up to the required number. I was having all sorts of fun when not supplying an “end date” before I found that! :wink:

Hey @takbb , I figured the Create Date&Time Range would do the trick since, as the name says, it should create Dates (and time) for a particular Range.

It is a shame indeed that there isn’t any dataflow as input - it’s the only reason why I had to use the loop.

Regarding your comment about the number of days and End Date, that is correct. It’s just that initially I wanted to use the End Date, but I could not get it running properly without specifying the number of rows. I know I could also specify the number of days, but got lazy having to convert the int to string, and then append “d”, so I just ended up using both End Date and number of days.

1 Like

Hi all

thank you for your support and for the time you’ve spent for helping me

R

1 Like

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