Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

thepope
Not applicable

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
Not applicable

Re: Looping and transforming records

see attachment

1.png

2 Replies
maxgro
Not applicable

Re: Looping and transforming records

see attachment

1.png

MarcoWedel
Not applicable

Re: Looping and transforming records

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