Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jpapador
Partner - Specialist
Partner - Specialist

Create intervals between a range

I have a start time and a stop time what I'd like to do is create records for every hour in between.

For example:

Current:

Record NumStart TimeStop Time
110:0013:27

What I want

Record Num

Hour interval
110:00
111:00
112:00
1

13:00

113:27
1 Solution

Accepted Solutions
maxgro
MVP
MVP

X:

LOAD

  [Record Num],

    [Start Time],

    [Stop Time],

  Time([Start Time] + 1/24 * (iterno()-1)) as Hour

FROM

[https://community.qlik.com/thread/240846]

(html, codepage is 1252, embedded labels, table is @1)

While [Start Time] + 1/24 * (iterno()-1) <=  [Stop Time]

;

Concatenate (X)

X:

First 1

LOAD

  [Record Num],

    [Start Time],

    [Stop Time],

  0+[Stop Time] as Hour

FROM

[https://community.qlik.com/thread/240846]

(html, codepage is 1252, embedded labels, table is @1)

;

View solution in original post

3 Replies
sunny_talwar

May be try this:

Table:

LOAD [Record Num],

  Time(RangeMin(Round([Start Time] - MakeTime(1) + MakeTime(IterNo()), 1/24), [Stop Time])) as [Hour Interval]

While Round([Start Time] - MakeTime(1) + MakeTime(IterNo()), 1/24) < [Stop Time] + MakeTime(1);

LOAD * INLINE [

    Record Num, Start Time, Stop Time

    1, 10:00, 13:27

];

maxgro
MVP
MVP

X:

LOAD

  [Record Num],

    [Start Time],

    [Stop Time],

  Time([Start Time] + 1/24 * (iterno()-1)) as Hour

FROM

[https://community.qlik.com/thread/240846]

(html, codepage is 1252, embedded labels, table is @1)

While [Start Time] + 1/24 * (iterno()-1) <=  [Stop Time]

;

Concatenate (X)

X:

First 1

LOAD

  [Record Num],

    [Start Time],

    [Stop Time],

  0+[Stop Time] as Hour

FROM

[https://community.qlik.com/thread/240846]

(html, codepage is 1252, embedded labels, table is @1)

;

shah101
Contributor
Contributor

  I tried you formula to create the interval and it worked for me but I need some help to create 30 minutes interval instead of 1 hour. I made some changes into the code and was able to create the interval for 24 hours.

Table:

LOAD [DateTime],

  Time(RangeMin(Round([Start Time] - MakeTime(1) + MakeTime(IterNo()), 1/24), [Stop Time])) as [Hour Interval]

While Round([Start Time] - MakeTime(1) + MakeTime(IterNo()), 1/) < [Stop Time] + MakeTime(1);

LOAD * INLINE [

    DateTime, Start Time, Stop Time

 any help will be appreciated!

    1, 00:00, 23::30

];