Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I am trying to figure out a way to populate a load statement with time-stamp values that fall between two time-stamps.
Here is an example:
LOAD *
INLINE[
LOG,Start,End
123,02/08/17 20:44,02/09/17 01:52
]
I would like to be able to see
02/08/17 20:44
02/08/17 20:45
02/08/17 20:46
etc
For each minute between the Start and End values.
Please let me know if you need any further info.
Thanks again
Try this:
LOAD LOG,
TimeStamp(Start + IterNo()/1440 - 1/1440) as Time
While Start + IterNo()/1440 - 1/1440 <= End;
LOAD * INLINE [
LOG, Start, End
123, 02/08/17 20:44, 02/09/17 01:52
];
Try this:
LOAD LOG,
TimeStamp(Start + IterNo()/1440 - 1/1440) as Time
While Start + IterNo()/1440 - 1/1440 <= End;
LOAD * INLINE [
LOG, Start, End
123, 02/08/17 20:44, 02/09/17 01:52
];
X:
LOAD
LOG,
Timestamp#(Start, 'MM/DD/YY hh:mm') as Start,
Timestamp#(End, 'MM/DD/YY hh:mm') as End
INLINE [
LOG,Start,End
123,02/08/17 20:44,02/09/17 01:52
124,02/08/17 20:45,02/09/17 01:53
]
;
load
LOG,
Timestamp(Start + (IterNo() - 1) / (24*60)) as NewField
Resident X
While (Start + (IterNo() - 1) / (24*60*60)) <= End;
This works with my inline load, however if I try to use it on a timestamp that I generate in the load statement is does not work. I would assume it has something to do with formatting?
Here is how I create the timestamp in the load statement:
Timestamp#
(Date(SURGERY_DATE) & ' ' &Time(interval([In Room Time]),'hh:mm'),'YYYY-MM-DD hh:mm') as StartDtTm,
if([In Room Hour]<= 23 AND [Out Room Hour]>=0,Timestamp#(Date(SURGERY_DATE+1) & ' ' &Time(interval([Out of Room Time]),'hh:mm'),'YYYY-MM-DD hh:mm'),Timestamp#(Date(SURGERY_DATE) & ' ' &Time(interval([Out of Room Time],'hh:mm')),'YYYY-MM-DD hh:mm')) as EndDtTm
Could this be creating an issue with the IterNo function?
May be try this
TimeStamp(SURGERY_DATE + [In Room Time], 'YYYY-MM-DD hh:mm') as StartDtTm,
If([In Room Hour] <= 23 and [Out Room Hour] >= 0,
TimeStamp(SURGERY_DATE + 1 + [Out of Room Time], 'YYYY-MM-DD hh:mm'),
TimeStamp(SURGERY_DATE + [Out of Room Time], 'YYYY-MM-DD hh:mm')) as EndDtTm
Source:
Load LOG,
Timestamp(Timestamp#(Start,'MM/DD/YY hh:mm'),'MM/DD/YY hh:mm') as Start,
Timestamp(Timestamp#(End,'MM/DD/YY hh:mm'),'MM/DD/YY hh:mm') as End;
LOAD * INLINE [
LOG,Start,End
123,02/08/17 20:44,02/09/17 01:52
];
Left Join ( Source)
Load LOG,
Start,
End,
Timestamp(Start + (Iterno()-1)/(24*60),'MM/DD/YY hh:mm') as minuteWiseTime
resident Source
While Start + Iterno()/(24*60) < End;
Perfect thanks much!