topic Re: Merge Continuous Intervals in QlikView

Merge Continuous Intervals

Tue, 04 Mar 2014 21:38:35 GMT

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.

ID	Start	End
1	1/1/2010	3/7/2010
1	1/1/2011	5/31/2011
1	10/30/2011	12/31/2011
1	1/1/2012	12/31/2012
1	1/1/2013	3/31/2013
1	5/13/2013	12/31/2013
1	1/1/2014	12/31/2014

I'm looking to get the following:

ID	Start	End
1	1/1/2010	3/7/2010
1	1/1/2011	5/31/2011
1	10/31/2011	3/13/2013
1	5/13/2013	12/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.

Re: Merge Continuous Intervals

swuehl — Tue, 04 Mar 2014 22:53:00 GMT

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;

Re: Merge Continuous Intervals

Wed, 05 Mar 2014 14:32:11 GMT

That did it. Thanks!