"holes" in a range of dates

[Anonymous]

Hi!

I have two fields that contains Dates, on field for "from dates" and one for "to dates". i'm trying to find holes in the date rage between from date and to date and from date and to date etc.

Hole > 1 date

For example, this is data for one item with several from dates and several to dates.

from date = 2015-02-03

to date = 2015-02-14

from date = 2015-02-15

to date = 2015-03-31

from date = 2015-04-04

to date = 2015-04-06

In the above example i have one hole between 2015-03-31 and 2015-04-04.

my question is, how do i find these holes?

script or not, every solution is welcome!

//A.

[Gysbert_Wassenaar]

Assuming your data is already sorted by from_date and you don't have overlapping intervals:

LOAD

     from_date,

     to_date,

     if(from_date - previous(to_date) > 1, 'Hole encountered')

FROM ...

[rubenmarin]

Hi, you can do momething like Data2 table in this script:

Data:

LOAD Date(Date#(from_date, 'YYYY-MM-DD'), 'DD/MM/YYYY') as from_date,

    Date(Date#(to_date, 'YYYY-MM-DD'), 'DD/MM/YYYY') as to_date;

LOAD * Inline [

from_date, to_date

2015-02-03, 2015-02-14

2015-02-15, 2015-03-31

2015-04-04, 2015-04-06

];

Data2:

NoConcatenate

LOAD from_date, to_date,

    from_date-Peek(to_date) as Daysdiff

Resident Data Order By from_date;

DROP Table Data;

Then wich DaysDiff>1, those are the dates that starts more than one day after the last to_date.

[Anonymous]

Hi, thanks. I do have overlapping intervals..

[rubenmarin]

Hi, then you can use:

LOAD

     from_date,

     to_date,

     if(previous(to_date)>to_date, previous(to_date), to_date) as Max_to_date,

     if(from_date - previous(Max_to_date) > 1, 'Hole encountered') as HoleDetector

FROM ...

[Anonymous]

Thanks!

Had to do an order by before the previous statements though.

But, i need to also have a "hole encountered" when the FOM date i the first FOM in the row. the start FOM so to speak. any ideas?

Thanks again!

//A.

[rubenmarin · Accepted solution]

Hi,  only for the first from_date? you can use RowNo(). And yes, you've to sort by from date.

Data:

LOAD Date(Date#(from_date, 'YYYY-MM-DD'), 'DD/MM/YYYY') as from_date,

    Date(Date#(to_date, 'YYYY-MM-DD'), 'DD/MM/YYYY') as to_date;

LOAD * Inline [

from_date, to_date

2015-02-03, 2015-02-14

2015-02-15, 2015-03-31

2015-04-04, 2015-04-06

];

Data2:

NoConcatenate

LOAD from_date,

     to_date,

     if(peek(to_date)>to_date, peek(to_date), to_date) as Max_to_date,

     if(RowNo()=1 or from_date - peek(Max_to_date) > 1, 'Hole encountered') as HoleDetector

Resident Data Order By from_date;

DROP Table Data;

[Anonymous]

Thank you!

[Anonymous]

I have another question related to this one.

In this case i want to establish he lenght between FOM and TOM, and se which ones that have a span longer than 30 days. So no gaps in the series of dates.

The problem being that the FOM and TOM can overlap.

//A.

[rubenmarin]

Hi I can't make tests today, you can get the difrences between dates, and 'transport' the FOM date until a hole is encountered.

Something like:

Data2:

NoConcatenate

LOAD from_date,

     to_date,

     if(peek(to_date)>to_date, peek(to_date), to_date) as Max_to_date,

     if(from_date - peek(Max_to_date) <= 1, Peek(from_date), from_date) as Min_from_date,

     if(from_date - peek(Max_to_date) <= 1, if(to_date - peek(Min_from_date)>30, 'Span >30')) as LongSpan,

     if(RowNo()=1 or from_date - peek(Max_to_date) > 1, 'Hole encountered') as HoleDetector

Resident Data Order By from_date;

Again, not tested, probably needs debug.