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.

Not applicable

Merge Continuous Intervals

So I'm looking to merge a few intervals into a single table in QV, and I was wondering if anyone had any ideas.

Right now I have a simple table that breaks out intervals, however, a new interval is created every year if it crosses the calendar year.

I need to figure out to merge these back into single intervals.  Does anyone have suggestions on where to start?

Ex:

I have a table with the following structure.

IDStart

End

11/1/20103/7/2010
11/1/20115/31/2011
110/30/201112/31/2011
11/1/201212/31/2012
11/1/20133/31/2013
15/13/201312/31/2013
11/1/201412/31/2014

I'm looking to get the following:

IDStartEnd
11/1/20103/7/2010
11/1/20115/31/2011
110/31/20113/13/2013
15/13/201312/31/2014

I have some rough ideas how to get there, but they all involve some fairly large joins and I was hoping to avoid that since the source table is several million records.

1 Solution

Accepted Solutions
MVP
MVP

Re: Merge Continuous Intervals

Maybe like

SET DateFormat='M/D/YYYY';

INPUT:

LOAD ID,

    Start,

    End,

    if(peek(End)+1=Start, peek(Period), autonumber(recno(),ID)) as Period

FROM

[http://community.qlik.com/thread/109476]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD ID, Date(min(Start)) as Start, Date(max(End)) as End

Resident INPUT

group by ID, Period;

drop table INPUT;

2 Replies
MVP
MVP

Re: Merge Continuous Intervals

Maybe like

SET DateFormat='M/D/YYYY';

INPUT:

LOAD ID,

    Start,

    End,

    if(peek(End)+1=Start, peek(Period), autonumber(recno(),ID)) as Period

FROM

[http://community.qlik.com/thread/109476]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD ID, Date(min(Start)) as Start, Date(max(End)) as End

Resident INPUT

group by ID, Period;

drop table INPUT;

Not applicable

Re: Merge Continuous Intervals

That did it.  Thanks!