Looping and transforming records

[thepope]

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.

[maxgro · Accepted solution]

see attachment

[MarcoWedel]

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