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:
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 🙂
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
load date(FieldValue('Start_Date_T1',RecNo())) as Date
load date(FieldValue('Start_Date_T2',RecNo())) as Date
...etc for the other start_date fields
//get the minimum date of all start dates
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
LOAD date($(vMinDate)+IterNo()-1) AS TEMPDATE
AutoGenerate(1) while num($(vMinDate)+IterNo()-1)<=num($(vMaxDate));
//create the final calendar table
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
drop Table CalenderWork;
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).
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:
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
(biff, embedded labels, table is F2$);
date(Start_Date_T1+IterNo()-1) as Date
While num(End_Date_T1) >= num(Start_Date_T1)+IterNo()-1;