Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Gestion-PSD
Creator II
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!!

 

Thanks in advance.

Best regards,

Alonso Torres

Labels (1)
1 Reply
Gestion-PSD
Creator II
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