Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
That did it. Thanks!