# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Partner

## Calculate part of interval not overlapping with other interval

Hi all

I am a bit stuck and was hoping one of the experts could help me.

I am busy calculating OEE and for that I need to find out which part of a batch was not overlapping with a time loss in the same shift. My mind works in pictures so maybe the following helps with the explanation:

What I essentially want is the duration green part of the batch which does not overlap with an orange time loss (or time losses) in that same shift (i.e. the amount of a batch which is blue in the picture).

Some rules:

1. Batches in the same shift cannot overlap.

2. Time losses in the same shift cannot overlap.

3. My example is divided into 30min blocks, but these are actually timestamps with seconds and not such fixed intervals.

The following inline tables create the Batch and TimeLoss entries in my example:

[Batch]:
ShiftId,Date#(StartTime,'YYYY/MM/DD hh:mm') AS StartTime,Date#(EndTime,'YYYY/MM/DD hh:mm') AS EndTime, Id
INLINE [
ShiftId,Id,StartTime,EndTime
1,1,2020/04/08 09:00,2020/04/08 11:00
1,2,2020/04/08 11:00,2020/04/08 13:30
1,3,2020/04/08 14:30,2020/04/08 15:30
1,4,2020/04/08 16:00,2020/04/08 17:30
2,5,2020/04/08 08:00,2020/04/08 09:30
2,6,2020/04/08 10:00,2020/04/08 13:00
2,7,2020/04/08 13:00,2020/04/08 15:00
2,8,2020/04/08 16:00,2020/04/08 17:00
];

[TimeLoss]:
ShiftId,Date#(StartTime,'YYYY/MM/DD hh:mm') AS StartTime,Date#(EndTime,'YYYY/MM/DD hh:mm') AS EndTime,Id
INLINE [
ShiftId,Id,StartTime,EndTime
1,1,2020/04/08 08:30,2020/04/08 09:30
1,2,2020/04/08 10:30,2020/04/08 11:30
1,3,2020/04/08 12:30,2020/04/08 13:00
1,4,2020/04/08 15:30,2020/04/08 18:00
2,5,2020/04/08 08:30,2020/04/08 11:30
2,6,2020/04/08 12:00,2020/04/08 13:30
2,7,2020/04/08 14:00,2020/04/08 16:00
2,8,2020/04/08 17:00,2020/04/08 18:00
];

I would like to end up with the Available Seconds column added to my batch table so that I can store it as a QVD:

 Batch ShiftId Id StartTime EndTime Available Seconds 1 1 2020/04/08 09:00 2020/04/08 11:00 1800 1 2 2020/04/08 11:00 2020/04/08 13:30 5400 1 3 2020/04/08 14:30 2020/04/08 15:30 3600 1 4 2020/04/08 16:00 2020/04/08 17:30 0 2 5 2020/04/08 08:00 2020/04/08 09:30 1800 2 6 2020/04/08 10:00 2020/04/08 13:00 1800 2 7 2020/04/08 13:00 2020/04/08 15:00 1800 2 8 2020/04/08 16:00 2020/04/08 17:00 3600

I have attached* the script which I created using the document attached to this article by @Henric_Cronström. However, this is as far as I've gotten.

Any help would be greatly appreciated.

Regards,

Mauritz

Labels (1)

0 Replies