Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
thepope
Contributor II
Contributor II

Looping and transforming records

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

2 Replies
maxgro
MVP
MVP

MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_136316_Pic1.JPG.jpg

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