Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
rubenmarin

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;

View solution in original post

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


talk is cheap, supply exceeds demand
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
Not applicable
Author

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
Not applicable
Author

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

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
Not applicable
Author

Thank you!

Anonymous
Not applicable
Author

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.