How to Implement a Date Island based on Start and End Dates

[#1]

Hi,

I needed some inputs on how to go about implementing a date insland for say, for a table that has records that are routinely updated using their start/end dates. Consider the following table:

ACCT	BLOCK_CODE	START_DATE	END_DATE
8547892135800048001	U	1/1/2012	12/31/2012
8547892135800048001	S	1/1/2013
8547892135800520009	U	1/1/2012
8547892135802808006	T	1/1/2012	8/29/2012
8547892135802818005	U	1/20/2012	8/29/2012
8547892135802828004	U	1/20/2012
8547892135802818005	S	8/30/2012
8547892135802808006	U	8/30/2012

Here if whenever there is a change in the records, a new row is added with the new start date as today's date and the old record is made obselete by adding an end date.

I need guidance on how to implement a date island across a data model containing many such tables whereby I can choose records across several such tables based on their active entries.

So if I select Jan 2013 in the date island, it should select rows wherever the records were active for this selection.

Thanks for your time 🙂

Sam.

[Gysbert_Wassenaar]

See this tutorial and this discussion. You can use the technique from the first tutorial without using a data island calendar. Just creating the vMinDate and vMaxDate variables in the scripts will be enough to be able to use the calendar objects and define the needed Select in Field actions.

If you use a data island calendar you still need to use Select in Field actions so that a selection in your calendar causes the selections in your start and end date fields to change accordingly.

If you decide to use a data island calendar you can create it with something like this added after your load statements:

//load all dates from all start_date fields in one table

Dates:

load date(FieldValue('Start_Date_T1',RecNo())) as Date

AutoGenerate FieldValueCount('Start_Date_T1');

load date(FieldValue('Start_Date_T2',RecNo())) as Date

AutoGenerate FieldValueCount('Start_Date_T2');

...etc for the other start_date fields

//get the minimum date of all start dates

MinDate:

Load min(Date) as MinDate Resident Dates;

//create two variables for a start and end date for the calendar

LET vMinDate = peek('MinDate');

LET vMaxDate = Today();

Drop Table MinDate;

//create a table with all dates between the start and end date

CalenderWork:

LOAD date($(vMinDate)+IterNo()-1) AS TEMPDATE

AutoGenerate(1) while num($(vMinDate)+IterNo()-1)<=num($(vMaxDate));

//create the final calendar table

Calender:

Load  Date(TEMPDATE) as CalDate,

      Month(TEMPDATE) as CalMonth,

      'Q' & Ceil(Month(TEMPDATE)/3) as CalQuarter,

      Year(TEMPDATE) as CalYear,

      Year(TEMPDATE)&'-'&Month(TEMPDATE) as CalYearMonth,

      Month(TEMPDATE)&'-'&Year(TEMPDATE) as CalMonthYear,         

      AutoNumber(MonthStart(TEMPDATE)) as CalMonthSerial,

      AutoNumber(WeekYear(TEMPDATE)) as CalWeekSerial,

      AutoNumber(QuarterStart(TEMPDATE)) as CalQuarterSerial

Resident CalenderWork;

drop Table CalenderWork;

[Anonymous · Accepted solution]

Dear SAM,

    Please check the attachment of qvw,according to my undarsatnd i did that one,

     Your masters are updated daily basis means it willbe correct.

Regards

[#4]

Thanks, Gysbert... Pretty useful to create a common calendar that way. Basha, your QVW file helped immensely. I've replicated this logic and it works fine.

But I would like to know if there is any way we could connect a date table to the source table containing start and end date ranges, without having to replicate the rows in the table for each date in the range (using interno() as you guys have done).

thanks again

Sam.

[#5]

Hi.. Guys, I think I got this.

I used interval match to do it. This way there aren't any replication of rows in the source table in memory. This was a concern because I have to load 70 million plus rows from just one table.

Here's the script I ended up using. Gysbert and Basha helped, thanks:

Table1:

LOAD

     ACCT,

     AMBS_USER_ACCT_NBR,

     AMBS_XFR_ACCT,

     AMBS_ACCT_TYPE,

     AMBS_OVERLIMIT_FLAG,

     AMBS_CONDITION_OF_ACCT,

     AMBS_STAT_CHNG_FLAG,

     Date(date(Start_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Start_Date_T1,

     if(len(End_Date) = 0,Date(Today(),'DD/MM/YYYY'),

     Date(date(End_Date,'MM/DD/YYYY'),'DD/MM/YYYY')) as End_Date_T1

FROM

file.xls

(biff, embedded labels, table is F2$);

NoConcatenate

Table2:

LOAD

date(Start_Date_T1+IterNo()-1) as Date

Resident Table1

While num(End_Date_T1) >= num(Start_Date_T1)+IterNo()-1;

Data:

IntervalMatch(Date) LOAD

Start_Date_T1, End_Date_T1

Resident Table1;

Thanks all...

Sam.

[Anonymous]

Dear Sam,

     Thanks  SAM,i learn new things from you.

Regards