Load ID, Date#(Begindate,'DD-MM-YYYY') as Begindate, Date#(Enddate,'DD-MM-YYYY') as Enddate inline
[ID, Begindate, Enddate
1, 01-01-2008, 26-5-2010
2, 05-06-2009, 27-5-2016
3, 05-06-2010, 31-12-2011
4, 01-01-2000, 15-08-2020
5, 05-09-2011, 21-10-2012
6, 05-07-2007, 31-11-2008
7, 08-08-2008, 07-08-2012
8, 11-10-2000, 15-03-2002
9, 04-03-2007, 03-04-2008
10, 04-04-2011, 31-12-2012];
// ========== Find lowest and highest date ==========
Load Begindate as TempDate resident DateRanges;
Load Enddate as TempDate resident DateRanges;
Load Min(TempDate) as MinDate, Max(TempDate) as MaxDate resident AllDates;
Let vMinDate = peek('MinDate',-1,'MinMaxDate') -1 ;
Let vMaxDate = peek('MaxDate',-1,'MinMaxDate') ;
Drop Tables AllDates, MinMaxDate ;
// ========== Generate all Dates ==========
Load *, Year(Date) as Year;
Load Date($(vMinDate) + recno()) as Date autogenerate $(vMaxDate) - $(vMinDate) ;
// ========== Perform an IntervalMatch ==========
IntervalMatch (Date) Load Begindate, Enddate resident DateRanges;
The first Load - DateRanges - just loads your data and makes sure that the dates are interpreted as numbers. Make a list box of a date and format it as number (List box properties-Number-Integer) then you'll see.
In AllDates I load all numbers in one field, so I can load the smallest and largest into the subsequent MinMaxDate table, which has only one record.
The two Let statements just load the smallest and largest value into variables. When this is done, I can drop both AllDates and MinMaxDate. I do not need them anymore.
Having these two variables, I can generate a table Dates containing all dates between the two variable values using a Load ... autogenerate.
Finally, the Intervalmatch is the magic: The intervalmatch transformation compares the ranges defined in DateRanges with the individual values in Dates and generates all combinations between the two.
And that's it...
Thanks for this.....i was also looking for this one & reached here on a single search........really useful..........
But could you please explain abt the 'ExistingDate'......which date/field is it ? and if i dnt use autogenerate then how the dates will be generated........actually i'm sill learnig its correct usage.
The intervalmatch is all about comparing one table with date ranges to another table with individual dates. (Dates or other types of numbers). In this case the table with ranges exist and the question is where to find the individual dates.
If you have them in a table, you can load this table and use these dates.
If you don't have the dates in a table, you need to generate them using e.g. a Load autogenerate.
In both cases, both the table with the ranges and the table with the dates need to be loaded before the Intervalmatch is done.
In djibiont's case, I assumed that there was no existing dates table - because it was not mentioned - so I suggested an autogenerate. But there was, and then it makes more sense to use this table than an autogenerate.
Thanks you for the reply Sir,
As u've said:
"If you have them in a table, you can load this table and use these dates."
Can i use the following code to get all the dates from my table:
distinct startdate as date resident tablename;
Load distinct enddate as date resident tablename;
and then intervalmatch:
interalmatch(date) load startdate,enddate resident tablename;
Will this work ?
actually i'm nt able to understand:
" it will only contain the dates that happen to be the start of end of some range. It will not contain the dates in between"
can u explain a bit more.......
no actually i want all the dates & not incomplete dates.........then i think i should remove distinct and then concatenate.
plz throw some light
If you want all the dates, then the table containing the "date" field must have one record per date. But your suggested solution will only load the dates that already exist in your table that contain the ranges. In other words, your dates table will have just some of the dates.
Ranges table: One record per date range.
Dates table: One record per date.
Intervalmatch table: One record per match between the two previous tables.
Try the script posted above with a Load ... autogenerate.