Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

apalmtree
Contributor II

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

Tags (1)
1 Solution

Accepted Solutions

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;

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;

16 Replies
MVP & Luminary
MVP & Luminary

Re: "holes" in a range of dates

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

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;

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.

apalmtree
Contributor II

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:

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

apalmtree
Contributor II

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;

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;

apalmtree
Contributor II

Re: "holes" in a range of dates

Thank you!

apalmtree
Contributor II

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

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.

Community Browser