16 Replies Latest reply: Mar 26, 2015 9:43 AM by Annika Palm

"holes" in a range of dates

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.

• Re: "holes" in a range of dates

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

from_date,

to_date,

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

FROM ...

• Re: "holes" in a range of dates

Hi, thanks. I do have overlapping intervals..

• Re: "holes" in a range of dates

Hi, then you can use:

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 ...

• Re: "holes" in a range of dates

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.

• Re: "holes" in a range of dates

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;

from_date, to_date

2015-02-03, 2015-02-14

2015-02-15, 2015-03-31

2015-04-04, 2015-04-06

];

Data2:

NoConcatenate

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;

• Re: "holes" in a range of dates

Thank you!

• Re: "holes" in a range of dates

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.

• Re: "holes" in a range of dates

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

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.

• Re: "holes" in a range of dates

I did minor changed for it to work, thanks!

but.

When the from_date and the to_date is the same (Forgot to tell you it could) it markes it as Span >30.

• Re: "holes" in a range of dates

thik i solved it... just added from_date <> to_date in the if statement.

• Re: "holes" in a range of dates

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;

from_date, to_date

2015-02-03, 2015-02-14

2015-02-15, 2015-03-31

2015-04-04, 2015-04-06

];

Data2:

NoConcatenate

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.

• Re: "holes" in a range of dates

I did found a case when this didn't work.

dates looking like this:

from_date          to_date

2015-01-07 2015-01-07

2015-01-08 2015-01-14

2015-01-15 2015-01-20

2015-01-20 2015-01-20

2015-01-21 2015-01-23

2015-01-24 2015-01-31

2015-02-01 2015-02-08

2015-02-09 2015-02-13

2015-02-14 2015-02-28

This is clearly a series of at least 30 days. But it doesn't mark it like that. I'm thinking maybe the tha fact that there's to FOM dates that are the same?
//A.
• Re: "holes" in a range of dates

Hi, in your last post you said "just added from_date <> to_date in the if statement."

Can you post your script to check and make some tests?

• Re: "holes" in a range of dates

tmp:

from_date,

to_date,

ITEM,

date,

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

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

Resident tmp

Order By ITEM, from_date;

Table:

from_date,

to_date,

ITEM,

date,

Max_to_date,

Min_from_date,

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

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

Resident tmp ;

drop table tmp;

• Re: "holes" in a range of dates

Hi, I removed ITEM and date (I have no data), you should put them again.

Problem was the Min_Date, please check attachment.

• Re: "holes" in a range of dates

thanks! works great! //A