4 Replies Latest reply: May 22, 2012 2:07 AM by Sergey Belostotsky RSS

    transform table (range of dates)

    Sergey Belostotsky

      Hi. I need to transform the table1 to result table (in a loading script). How can i do that? Thanks in advance!

       

      table1:

       

      ActionName

      DateStartDateEnd
      Акция 101.01.201205.01.2012
      Акция 201.01.201207.01.2012
      Акция 330.12.201102.01.2012

       

       

      Result table:

       

      ActionNameDateStartDateEndDate
      Акция 101.01.201205.01.201201.01.2012
      Акция 101.01.201205.01.201202.01.2012
      Акция 101.01.201205.01.201203.01.2012
      Акция 101.01.201205.01.201204.01.2012
      Акция 101.01.201205.01.201205.01.2012
      Акция 201.01.201207.01.201201.01.2012
      Акция 201.01.201207.01.201202.01.2012
      Акция 201.01.201207.01.201203.01.2012
      Акция 201.01.201207.01.201204.01.2012
      Акция 201.01.201207.01.201205.01.2012
      Акция 201.01.201207.01.201206.01.2012
      Акция 201.01.201207.01.201207.01.2012
      Акция 330.12.201102.01.201230.12.2011
      Акция 330.12.201102.01.201231.12.2011
      Акция 330.12.201102.01.201201.01.2012
      Акция 330.12.201102.01.201202.01.2012
        • Re: transform table (range of dates)
          Miguel Angel Baeyens de Arce

          Hi Sergey,


          Take a look at the following script. I'm using two variables vMinDate and vMaxDate to create a dummy table labelled "Calendar" that has all possible dates between the lowest and highest date of the Data table. Then using the IntervalMatch() load and JOIN, the table is completed with each range discrete date.

           

          Data:
          LOAD * INLINE [
          ActionName, DateStart, DateEnd
          Акция 1, 01/01/2012, 05/01/2012
          Акция 2, 01/01/2012, 07/01/2012
          Акция 3, 30/12/2011, 02/01/2012
          ];
          
          LET vMinDate = Num(Date('30/12/2011'));
          LET vMaxDate = Num(Date('07/01/2012'));
          
          Calendar:
          LOAD Date($(vMinDate) + RecNo() -1) AS Date
          AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;
          
          FullTable:
          JOIN (Data) INTERVALMATCH (Date) LOAD DateStart, DateEnd
          RESIDENT Data;
          
          DROP TABLE Calendar;
          

           

          Hope that helps.

           

          Miguel

          • Re: transform table (range of dates)
            Sokkorn Cheav

            Hi,

             

            Let try

             

            [Data]:
            LOAD RecNo()    AS [No],*;
            LOAD * INLINE [
            ActionName,    DateStart,    DateEnd
            Акция 1,    01-01-2012,    05-01-2012
            Акция 2,    01-01-2012,    07-01-2012
            Акция 3,    30-12-2011,    02-01-2012];
            
            SET vRecCount = NoOfRows('Data')-1;
            FOR i = 0 TO $(vRecCount)
                vMinDate = NUM(PEEK('DateStart',$(i),'Data'));
                vMaxDate = NUM(PEEK('DateEnd',$(i),'Data'));
                vNo = PEEK('No',$(i),'Data');
                IF i = 0 THEN
                    recCount = 0;
                ELSE
                    recCount = NoOfRows('DataCalendar');
                ENDIF
            
                DataCalendar:
                LOAD $(vNo) AS No,      
                  Date($(vMinDate) + RowNo()-$(recCount)-1) AS Dates
                AUTOGENERATE 1
                WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);
            NEXT
            
            NoConcatenate
            [Table1]:
            LOAD * RESIDENT Data;
            RIGHT JOIN
            LOAD * RESIDENT DataCalendar;
            
            DROP TABLE DataCalendar,Data;
            

            See sample attached file.

             

            Regards,

            Sokkorn

            • Re: transform table (range of dates)

              Hi,

               

              Try this code may be it can help you.

               

              DATA:
              LOAD * INLINE [
                  ActionName, Date Start, Date End
                  ABC, 01/01/2012, 05/01/2012
                  PQR, 03/01/2012, 07/01/2012
                  XYZ, 05/01/2012, 08/01/2012
              ];


              TEST:
              LOAD * INLINE [
                  DATES
                  01/01/2012
                  02/01/2012
                  03/01/2012
                  04/01/2012
                  05/01/2012
                  06/01/2012
                  07/01/2012
                  08/01/2012
                  09/01/2012
                  10/01/2012
              ];

              inner join
              IntervalMatch ( DATES ) LOAD [Date Start],[Date End] RESIDENT DATA;

              inner join
              LOAD *
              RESIDENT DATA;

              DROP TABLE DATA;

               

               

              Thanks & Best Regards,

              Kuldeep Tak

              • Re: transform table (range of dates)
                Sergey Belostotsky

                thank you very much, guys! all answers is correct,

                1 method  good for a small amount of lines (need a lot of memory)

                2 method dont need much memory but takes a lot of time.