Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Num | Start Time | Stop Time |
---|---|---|
1 | 10:00 | 13:27 |
What I want
| Hour interval | |
---|---|---|
1 | 10:00 | |
1 | 11:00 | |
1 | 12:00 | |
1 | 13:00 | |
1 | 13:27 |
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)
;
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
];
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)
;
Sunny_talwar 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
];