# App Development

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean:
Creator II

## Merge times with timetable to obtain effective time

Hi all!

I'll try to explain the puzzle I'm trying to solve.

We've a list of events wiht event_time and event_type which we get events duration:

 Event EventTime Old Value NewValue Creation 2022-04-25 10:25:00 - - Status Change 2022-04-26 17:51:00 Open Solved

With those events, we can calculate a solution time of 2022-04-26 17:51 - 2022-04-25 10:25 = 1886 minutes= 1 day, 7 hours, 26 minutes. Fine.

But we now have a problem: This solution time is sobreestimated because it counts non-wokring hours, so the solution time isn't real.

Out working time is form 08:00 to 18:00. So, time between 18:00 and 08:00 wouldn't count in solution times.

So, what I'm looking for is (for that example) to have a solution time of

(2022-04-25 18:00 - 2022-04-25 10:25) + (2022-04-26 17:51 - 2022-04-26 08:00) = 1046 minutes= 17 hours, 26 minutes.

But I don't know hot to reach it.

I'd tried with IntervalMatch with working timetable and events time:

 Event EventTime Start End Creation 2022-04-25 10:25 2022-04-25 08:00 22-04-25 18:00 Status Change 2022-04-26 17:51 2022-04-26 08:00 22-04-26 18:00

How can I select correct subinterval?

If I play with data, I can get subsegments start -> event time and event time -> end,

 Event Event time StartSubsegment EndSubSegment Creation 2022-04-25 10:25 2022-04-25 10:25 22-04-25 18:00 Status Change 2022-04-26 17:51 2022-04-26 17:51 22-04-26 18:00 Creation 2022-04-25 10:25 2022-04-25 08:00 2022-04-25 10:25 Status Change 2022-04-26 17:51 2022-04-26 08:00 2022-04-26 17:51

but how can I get only the subsegments I need (colored) ?

Maybe is better to take another approax, but i'm lost!!

Best regards,

Alonso Torres

Labels (1)
• ### General Question

Creator II
Author

Hi all again.

Maybe, another aprroach is to expand events table with wkorking time starts and ends, and later use Peek() + If() to mark the rows we need.

 Event Event time Old Value New Value woking time start 2022-04-25 08:00 - - creation 2022-04-25 10:25 - - working time end 2022-04-25 18:00 - - working time start 2022-04-26 08:00 - - status change 2022-04-26 17:51 open solved working time end 2022-04-26 18:00 - -

The problem is that I have no idea of How to create the "working time events", and how to select only needed rows.

Regards,

Alonso Torres

Tags
Community Browser