Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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

see attachment

1.png

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