Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
....
Might be worth to check the settings of date/time-formats in order to ensure that the times are interpreted correctly.
Peter
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
....