Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Mauritz_SA
Partner
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:

Logsheet_with_batches.png

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]:
LOAD
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]:
LOAD
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    
ShiftIdIdStartTimeEndTimeAvailable Seconds
112020/04/08 09:002020/04/08 11:001800
122020/04/08 11:002020/04/08 13:305400
132020/04/08 14:302020/04/08 15:303600
142020/04/08 16:002020/04/08 17:300
252020/04/08 08:002020/04/08 09:301800
262020/04/08 10:002020/04/08 13:001800
272020/04/08 13:002020/04/08 15:001800
282020/04/08 16:002020/04/08 17:003600

 

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