IterNo with Timestamps

[andrewmo]

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

[sunny_talwar · Accepted solution]

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

];

[maxgro]

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;

[andrewmo]

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?

[sunny_talwar]

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

[Digvijay_Singh]

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;

[andrewmo]

Perfect thanks much!