topic Re: Getting intervals from subsequent timestamps in QlikView

Getting intervals from subsequent timestamps

Anonymous — Thu, 20 Sep 2018 19:50:34 GMT

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.

Re: Getting intervals from subsequent timestamps

MarcoWedel — Thu, 20 Sep 2018 22:08:48 GMT

Hi,

(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

Re: Getting intervals from subsequent timestamps

Anonymous — Fri, 21 Sep 2018 17:33:39 GMT

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).

Re: Getting intervals from subsequent timestamps

MarcoWedel — Fri, 21 Sep 2018 21:04:31 GMT

Hi,

a script only and a front end solution could be:

straight table:

table box:

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

Re: Getting intervals from subsequent timestamps

Anonymous — Tue, 25 Sep 2018 18:13:46 GMT

It worked, Marco!

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

Thank you very much for helping out a newbie.

Regards,

Frederico