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.

16 Replies
Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

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?

Anonymous
Not applicable
Author

tmp:

LOAD

  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:

LOAD

  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;

rubenmarin

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

Problem was the Min_Date, please check attachment.

Anonymous
Not applicable
Author

thanks! works great! //A