Autogenerate while loop with hourly increment

[nougatitati]

I'm trying to generate hour-by-hour timestamps for each hour between two dates.

To do this, I take a functional script for doing the equivalent problem, which instead of hour-by-hour, it generates a timestamp for each day between the two specified dates.

This results, as expected, with this:

I attempt to amend this script to have an addition of hours rather than days:

However this results in an unusually long load time, that never seems to end:

Since it is a while loop, the terminating condition would be a candidate for the problem. However making test variables and checking the result of adding hours does indicate this process does work in isolation:

Results in:

As expected.

Why is the addition failing in the while loop, causing it to never terminate?

[nougatitati · Accepted solution]

I figured it out, and I'm posting the solution here just incase somebody finds it relevant to their work.

The problem is that I was adding a "Time#(IterNo() - 1, 'h')", which indeed increments the hour, for some reason it is not designed to be able to add to a given day to progress to the following day. To illustrate:

• 29/08/19 22:00 + Time#(1, 'h') evaluates to 29/08/19 23:00, however
• 29/08/19 23:00 + Time#(1, 'h') evaluates to 29/08/19 00:00

The way to remedy this is to perform the addition using the interval function instead. This will elicit the required results:

• 29/08/19 22:00 + Interval#(1, 'h') evaluates to 29/08/19 23:00, and as required,
• 29/08/19 23:00 + Interval#(1, 'h') evaluates to 30/08/19 00:00

When the 'interval' function is placed in the while condition, the loop will terminate as required.

[mikael-c]

Only problem for was that the loop would end one hour short.

For example, if vToday = 21/12/2021 08:00:00, then the max timestamp in the AddingHours table outcomes to 21/12/2021 07:00:00. I assume this is because of the interval function.

Simply corrected it with

... <= ($(vToday) + time#(1, 'h'))

as indicated in your original post.

Thanks a lot for this!

[AC_RPLC_Qlik]

Hi,

This is a really useful thread, I found a slightly less complex way of calculating the same thing:

Let v_date_full = Timestamp(Today() - 1);

[Date_Table]:
LOAD
	Timestamp(('$(v_date_full)') + Interval#((IterNo()-1), 'h')) as [timestamp]
    
    AutoGenerate 1 While IterNo()<=24;

Result:

Hope this is helpful.

Thanks!