topic Looping and transforming records in QlikView

Looping and transforming records

thepope — Fri, 03 Oct 2014 13:16:03 GMT

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.

Re: Looping and transforming records

maxgro — Fri, 03 Oct 2014 18:42:21 GMT

see attachment

Re: Looping and transforming records

MarcoWedel — Fri, 03 Oct 2014 23:03:15 GMT

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