topic Re: IterNo with Timestamps in QlikView

IterNo with Timestamps

andrewmo — Mon, 13 Mar 2017 19:18:02 GMT

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

Re: IterNo with Timestamps

sunny_talwar — Mon, 13 Mar 2017 19:28:54 GMT

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

];

Re: IterNo with Timestamps

maxgro — Mon, 13 Mar 2017 19:30:01 GMT

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;

Re: IterNo with Timestamps

andrewmo — Mon, 13 Mar 2017 19:40:26 GMT

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?

Re: IterNo with Timestamps

sunny_talwar — Mon, 13 Mar 2017 19:49:23 GMT

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

Re: IterNo with Timestamps

Digvijay_Singh — Mon, 13 Mar 2017 19:53:34 GMT

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;

Re: IterNo with Timestamps

andrewmo — Mon, 13 Mar 2017 19:55:57 GMT

Perfect thanks much!