Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a brain freeze at the moment and need some help.
I have log files that need transforming
Currently they are as follows
dID EventOpen EventClosed Duration
100 2014-01-31 17:23:40 2014-02-11 11:23:53 258:00:13
and I need formating
dID EvtDate Duration
100 2014-01-31 17:23:40 06:36:20
100 2014-02-01 00:00:00 24:00:00
100 2014-02-02 00:00:00 24:00:00
and so on untill
100 2014-02-11 11:23:53 11:23:53.
I know its case of looping through I just can't get my head around it at the mo.
see attachment
see attachment
Hi,
one solution might be:
LOAD dID,
Timestamp(If(Floor(EventOpen+IterNo()-1)<Floor(EventClosed),NumMax(Floor(EventOpen+IterNo()-1),EventOpen),EventClosed)) as EvtDate,
Interval(If(IterNo()=1,NumMin(Floor(EventOpen)+1,EventClosed)-EventOpen,If(Floor(EventOpen+IterNo()-1)<Floor(EventClosed),1,Frac(EventClosed)))) as Duration
INLINE [
dID, EventOpen, EventClosed, Duration
100, 2014-01-31 17:23:40, 2014-02-11 11:23:53, 258:00:13
]
While Floor(EventOpen+IterNo()-1)<EventClosed;
hope this helps
regards
Marco