5 Replies Latest reply: Jan 28, 2013 12:30 PM by khadar basha RSS

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

      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:

       

       

      ACCTBLOCK_CODESTART_DATEEND_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.

       

       

       

       

        • Re: How to Implement a Date Island based on Start and End Dates
          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;

          • Re: How to Implement a Date Island based on Start and End Dates
            khadar basha

            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

             

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

              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.

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

                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.

                • Re: How to Implement a Date Island based on Start and End Dates
                  khadar basha

                  Dear Sam,

                   

                       Thanks  SAM,i learn new things from you.

                   

                   

                   

                  Regards