Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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).
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
It worked, Marco!
I didn't know the "Previous" function. Great!
Thank you very much for helping out a newbie.
Regards,
Frederico