Problem statement: I am trying to solve a problem of continuity in time ranges and create a flag(continuous/gap).The goal is to evaluate all time values pairs and make sure there is no gap between time 11(start time) and time 42(end time). These time range sets will possibly go up in real world. So there might even be a Time81 and Time82 pair as well. My dataset looks like this: Primary KeyDateTime11Time12Time21Time22Time31Time32Time41Time42Flag111/11/20197:00:0013:00:007:00:0015:00:009:00:0018:00:0013:00:0021:00:00Continous211/12/20198:00:0018:00:009:00:0015:00:0015:30:0021:30:00 Continous311/13/20198:00:0017:00:009:45:0015:45:0016:00:0021:00:00 Continous411/14/20198:00:0013:30:0014:00:0021:00:00 Gap511/15/20198:00:0013:30:0012:00:0014:00:0014:30:0018:00:00 Gap I was trying to work with something like this but I am getting to many false positives. if DateTimeDiff([Timings21],[Timings12],"minutes")>0 or DateTimeDiff([Timings31],[Timings22],"minutes")>0 or DateTimeDiff([Timings41],[Timings32],"minutes")>0 or DateTimeDiff([Timings31],[Timings12],"minutes")<0 then 'Gap' else 'Continuous' endif Any help or guidance is highly appreciated. Let me know if there is more clarity needed. Thanks in advance.

Calculate gap in a set of time ranges

Problem statement:

I am trying to solve a problem of continuity in time ranges and create a flag(continuous/gap).The goal is to evaluate all time values pairs and make sure there is no gap between time 11(start time) and time 42(end time). These time range sets will possibly go up in real world. So there might even be a Time81 and Time82 pair as well.

My dataset looks like this:

Primary Key	Date	Time11	Time12	Time21	Time22	Time31	Time32	Time41	Time42	Flag
1	11/11/2019	7:00:00	13:00:00	7:00:00	15:00:00	9:00:00	18:00:00	13:00:00	21:00:00	Continous
2	11/12/2019	8:00:00	18:00:00	9:00:00	15:00:00	15:30:00	21:30:00			Continous
3	11/13/2019	8:00:00	17:00:00	9:45:00	15:45:00	16:00:00	21:00:00			Continous
4	11/14/2019	8:00:00	13:30:00	14:00:00	21:00:00					Gap
5	11/15/2019	8:00:00	13:30:00	12:00:00	14:00:00	14:30:00	18:00:00			Gap

I was trying to work with something like this but I am getting to many false positives.

if
DateTimeDiff([Timings21],[Timings12],"minutes")>0
or
DateTimeDiff([Timings31],[Timings22],"minutes")>0
or
DateTimeDiff([Timings41],[Timings32],"minutes")>0
or
DateTimeDiff([Timings31],[Timings12],"minutes")<0
then
'Gap'
else
'Continuous'
endif

Any help or guidance is highly appreciated. Let me know if there is more clarity needed.

Thanks in advance.

Workflow

Date Time

Transformation

Accepted answers

philipmannering

If I've understood you correctly (which I almost certainly haven't) you could try this,

Workflow

gaps in datetimes.yxmd

All comments

estherb47

Hi @arsh6013

A little more clarity would be helpful. Are you looking to compare all time pairings to see if there is a difference? Are you looking for a pair to pair gap vs. continuous, or gap/continuous for all of the pairs.

A quick look at this dataset and I'm still unclear at what makes something continuous or gap, because there are gaps in all of the different time pairs when you look across.

thank you & cheers!

Esther

philipmannering

If I've understood you correctly (which I almost certainly haven't) you could try this,

Workflow

gaps in datetimes.yxmd

arsh6013

@PhilipMannering That is exactly what I was looking for. The method works great even if I have to increase the time slots to more than 4.

@Esther

The comparison was suppose to happen across all pairs and flag if there is any gap in between the time ranges( The highest and the lowest time are the bounds in which the gap needs to be validated)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703