Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | ||||
---|---|---|---|---|---|---|---|
| U |
|
| ||||
| S |
| |||||
| U |
| |||||
| T |
|
| ||||
| U |
|
| ||||
| U |
| |||||
| S |
| |||||
| U |
|
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.
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
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;
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
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.
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.
Dear Sam,
Thanks SAM,i learn new things from you.
Regards