Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Getting intervals from subsequent timestamps

Hi!

I have the following table :

issue_id   |   journal_id    |    event_date    |     previous_status   |     status

1                    1              10:02:05 09-15-2018             1                     2

1                    2              15:01:25 09-16-2018             2                     3

1                    3              09:06:18 09-17-2018             3                     4

2                   10             13:12:15 09-11-2018             1                     2

2                   11             18:11:44 09-13-2018             2                     3

....

I need to generate another table with the following structure:

issue_id | total_duration (seconds)

1                 XXX             

2                 YYY

Where "total_duration" would be the accumulated difference among all the subsequent "event_date" associated with each issue_id.

Ex: issue_id = 1

total_duration = (third event_date - second event_date) + (second event_date - first event_date)

I really don't have enough knowledge of iterations in QlikSense... Does anybody know how to do it?


Thanks in advance.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

a script only and a front end solution could be:

straight table:

QlikCommunity_Thread_314580_Pic1.JPG

QlikCommunity_Thread_314580_Pic3.JPG

table box:

QlikCommunity_Thread_314580_Pic2.JPG

QlikCommunity_Thread_314580_Pic4.JPG

tabEventsTemp:

LOAD issue_id,

    journal_id,

    Timestamp#(event_date,'hh:mm:ss MM-DD-YYYY') as event_date,

    previous_status,

    status

Inline [

    issue_id, journal_id, event_date, previous_status, status

    1, 1, 10:02:05 09-15-2018, 1, 2

    1, 2, 15:01:25 09-16-2018, 2, 3

    1, 3, 09:06:18 09-17-2018, 3, 4

    2, 10, 13:12:15 09-11-2018, 1, 2

    2, 11, 18:11:44 09-13-2018, 2, 3

];

tabEvents:

LOAD issue_id,

    journal_id,

    event_date,

    If(issue_id=Previous(issue_id),Interval(Previous(event_date)-event_date,'d hh:mm:ss')) as duration,

    previous_status,

    status

Resident tabEventsTemp

Order By issue_id, event_date desc;

DROP Table tabEventsTemp;

tabEventDurations:

LOAD issue_id,

    Interval(Sum(duration),'s') as [total_duration (seconds)]

Resident tabEvents

Group By issue_id;

hope this helps

regards

Marco

View solution in original post

4 Replies
MarcoWedel

Hi,

as


(third event_date - second event_date) + (second event_date - first event_date)


equals


third event_date - first event_date


one solution might be:

Interval(Max(event_date)-Min(event_date))


hope this helps


regards


Marco

Anonymous
Not applicable
Author

Hi Marco!


Thanks for your answer. I wish it was just that, but in fact there are some ignored state transitions in the ETL process. There are some states that are not considered for the calculation. Therefore, among the first and the last state may be some intervals that are not considered (Sorry for not be very clear about that in the example).

MarcoWedel

Hi,

a script only and a front end solution could be:

straight table:

QlikCommunity_Thread_314580_Pic1.JPG

QlikCommunity_Thread_314580_Pic3.JPG

table box:

QlikCommunity_Thread_314580_Pic2.JPG

QlikCommunity_Thread_314580_Pic4.JPG

tabEventsTemp:

LOAD issue_id,

    journal_id,

    Timestamp#(event_date,'hh:mm:ss MM-DD-YYYY') as event_date,

    previous_status,

    status

Inline [

    issue_id, journal_id, event_date, previous_status, status

    1, 1, 10:02:05 09-15-2018, 1, 2

    1, 2, 15:01:25 09-16-2018, 2, 3

    1, 3, 09:06:18 09-17-2018, 3, 4

    2, 10, 13:12:15 09-11-2018, 1, 2

    2, 11, 18:11:44 09-13-2018, 2, 3

];

tabEvents:

LOAD issue_id,

    journal_id,

    event_date,

    If(issue_id=Previous(issue_id),Interval(Previous(event_date)-event_date,'d hh:mm:ss')) as duration,

    previous_status,

    status

Resident tabEventsTemp

Order By issue_id, event_date desc;

DROP Table tabEventsTemp;

tabEventDurations:

LOAD issue_id,

    Interval(Sum(duration),'s') as [total_duration (seconds)]

Resident tabEvents

Group By issue_id;

hope this helps

regards

Marco

Anonymous
Not applicable
Author

It worked, Marco!


I didn't know the "Previous" function. Great!

Thank you very much for helping out a newbie.

Regards,

Frederico