Generate new table based on date ranges in existing table?

[#1]

Hi All

I have a table where each row with a start date an end date and a few other metrics.  What I want to acheive in the load is to then split out these days into individual rows in a new table with a single date column and linking the same metrics.  I'll try to illustrate below:

Exiting table:

Start_Date     End_Date          Name     Address          etc

01/04/2010     05/04/2010        Fred       1 Anystreet 

01/02/2010     03/02/2010        Bob        2 AnotherStreet 

Desired Result:

Date              Name     Address          etc

01/04/2010     Fred       1 Anystreet

02/04/2010     Fred       1 Anystreet

03/04/2010     Fred       1 Anystreet

04/04/2010     Fred       1 Anystreet

05/04/2010     Fred       1 Anystreet

01/02/2010     Bob        2 AnotherStreet

02/02/2010     Bob        2 AnotherStreet

03/02/2010     Bob        2 AnotherStreet

Please can anyone advise how I could achieve this result?

Thanks

Dominic

[Clever_Anjos]

Check you manual for  IntervalMatch

[#3]

Thanks for the reply. 

I've taken a look and it doesn't quite match my situation.  I don't have 2 tables to match, I have a single table I want to split out into days for each line.

Appreciate the help.

Dominic

[Jason_Michaelides]

You need to use a combination of Autogenerate(), While and Peek()

I'll put together a script and post shortly.

[Jason_Michaelides]

Actually - you just need Peek()...

[#6]

Thanks Jason, look forward to seeing the script.

[Clever_Anjos]

Use this example to generate a Calendar Table and then use it in IntervalMatch

/*******************************************************************
This example shows some common patterns for generating a
table of dates.
*******************************************************************/

/*
Set the ending date. Note the use of the num() function to make the loop work correctly.
The End Date can be set using other functions like, MakeDate(). Just make sure it's a number.
*/
LET vEndDate = num(today(1)); // End at today's date

/*
Set the start date. Subtract 1 to simplify the LOAD loop.
Again, make sure the value is a number by using num().
*/
LET vStartDate = num(MakeDate(2006,1,1)) -1; // Start at 2006-01-01.
/* Some other variations for setting the start date: */
//  --12 months back from EndDate
//LET vStartDate = num( addMonths($(vEndDate),-12) );
//  --Beginning of the current year
//LET vStartDate = num( yearStart(today(1)) ) -1;
//  --Beginning of the prior year
//LET vStartDate = num( yearStart(addMonths($(vEndDate),-12)) ) -1;

/*
Note: Each example LOAD below generates a seperate table for
demonstration purposes. In a real application, there is typically only one date table.
*/

/*
Generate the date table using iterNo() to control the loop.
*/
DateMaster:
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;

/*
This example adds addtional dimensions to the table.
It's simplest to do this with a preceeding LOAD.
*/

DateMaster2:
LOAD
Date,
year(Date) as Year,
month(Date) as Month,
day(Date) as Day,
weekday(Date) as Weekday
;
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;

[jonathandienst · Accepted solution]

Dominic

Given a start and end date for your analysis, loop over all the dates to bring in the rows by day:

For zi = zStartDate To zEndDate
           Let zDate = Date(zi);
          Result_Table:
          LOAD Date($(zi)) As Date,
                    Name,
                    Address1, ...
           Resident ExistingTable
          Where Start_Date <= Date($(zi)) And End_Date >= Date($(zi))
 Next

This script fragment assumes that zStartDate and zEndDate are numeric and that your source data is in ExistingTable. Adjust as required.

You could use fixed dates for start and end, or use the minimum and maximum dates from your source data.

Regards

Jonathan

[Jason_Michaelides]

Jonathan's solution is much better than my idea!  Have attached a finished solution using his idea (seeing as I was doing one)...

Jason

[#10]

Thanks for the replies.  Finished work for today, will try implementing tomorrow and feedback.  Dominic