intervalmatch

[kamielrajaram]

Good day,

Please could someone tell what is wrong with this load statement as the rows are blank under time from and time to.

Thank you

LOAD STATEMENT------

Comments:

load

id,

date(floor(date(mydate))) as mydate,

time(mydate,'hh:mm:ss') as mytime

from

[myfile.qvd] (qvd);

Times:
LOAD * INLINE [
TimeFrom, TimeTo, TimeDesc
00:00:00, 00:29:59, 00:00:00
00:30:00, 00:59:59, 00:30:00
01:00:00, 01:29:59, 01:00:00
01:30:00, 01:59:59, 01:30:00
02:00:00, 02:29:59, 02:00:00
02:30:00, 02:59:59, 02:30:00
03:00:00, 03:29:59, 03:00:00
03:30:00, 03:59:59, 03:30:00
04:00:00, 04:29:59, 04:00:00
04:30:00, 04:59:59, 04:30:00
05:00:00, 05:29:59, 05:00:00
05:30:00, 05:59:59, 05:30:00
06:00:00, 06:29:59, 06:00:00
06:30:00, 06:59:59, 06:30:00
07:00:00, 07:29:59, 07:00:00
07:30:00, 07:59:59, 07:30:00
08:00:00, 08:29:59, 08:00:00
08:30:00, 08:59:59, 08:30:00
09:00:00, 09:29:59, 09:00:00
09:30:00, 09:59:59, 09:30:00
10:00:00, 10:29:59, 10:00:00
10:30:00, 10:59:59, 10:30:00
11:00:00, 11:29:59, 11:00:00
11:30:00, 11:59:59, 11:30:00
12:00:00, 12:29:59, 12:00:00
12:30:00, 12:59:59, 12:30:00
13:00:00, 13:29:59, 13:00:00
13:30:00, 13:59:59, 13:30:00
14:00:00, 14:29:59, 14:00:00
14:30:00, 14:59:59, 14:30:00
15:00:00, 15:29:59, 15:00:00
15:30:00, 15:59:59, 15:30:00
16:00:00, 16:29:59, 16:00:00
16:30:00, 16:59:59, 16:30:00
17:00:00, 17:29:59, 17:00:00
17:30:00, 17:59:59, 17:30:00
18:00:00, 18:29:59, 18:00:00
18:30:00, 18:59:59, 18:30:00
19:00:00, 19:29:59, 19:00:00
19:30:00, 19:59:59, 19:30:00
20:00:00, 20:29:59, 20:00:00
20:30:00, 20:59:59, 20:30:00
21:00:00, 21:29:59, 21:00:00
21:30:00, 21:59:59, 21:30:00
22:00:00, 22:29:59, 22:00:00
22:30:00, 22:59:59, 22:30:00
23:00:00, 23:29:59, 23:00:00
23:30:00, 23:59:59, 23:30:00
];

left join (Comments)
IntervalMatch (mytime)
load TimeFrom, TimeTo resident Times;

left join (Comments)
Load * Resident Times;

drop table Times;

[prieper]

Might be worth to check the settings of date/time-formats in order to ensure that the times are interpreted correctly.

Peter

[#3 · Accepted solution]

Try this:

....
Times:
LOAD
     time(TimeFrom) as TimeFrom,
     time(TimeTo) as TimeTo,
     TimeDesc
;
LOAD * INLINE [
TimeFrom, TimeTo, TimeDesc
00:00:00, 00:29:59, 00:00:00
00:30:00, 00:59:59, 00:30:00
....