3 Replies Latest reply: Feb 11, 2014 1:13 PM by Gonzalo Bianchi RSS

    Create a table from date ranges.

    Gonzalo Bianchi

      Hello:

       

      I have a set of data (32m records) that has a start and end years each record.

       

      Using this for any type of calculation is very slow and very complicated. We need to know if a particular record feels in a particular year.

       

      For this, we are trying to build a table that has a row that contain the key fields and a year. That year would be all the years between start and end. so if a particular records goes from 2003 to 2012, we will have a table that has

       

      KeyYear
      key2003
      key2004
      ......
      key2012

       

      We alredy try two ways to do this, and all of them works. But they take more than 6 hours to run (a lot more).

       

      One of them goes all the table all the time adding one year at a time for each key.

       

      the other has all the diference between start and end, builds a table that has all the years, and then concatenate it to the original table. This works better than the other one, but takes around a minute for each start-end row (and they are about 3500 in a select disticnt)...

       

      So any one has a better idea on how to build this table?

       

      Thanks.

        • Re: Create a table from date ranges.
          Gysbert Wassenaar

          Typically it's done with something like this:

           

          LOAD Key, StartYear + iterno() -1 as Year

          FROM MyTableWith32MRecords.qvd (qvd)

          while StartYear + iterno() -1 <= EndYear;

          • Re: Create a table from date ranges.
            Massimo Grossi

            Id doesn't seems so heavy, I just tried with this script (only 4M, not 32) and it only took me 90 seconds, including rows generation and qvd store, the log is attached

            or I didn't understand?

             

             

            directory;

             

            Characters:

            Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;

             

            ASCII:

            Load

            if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,

            Chr(RecNo()) as AsciiAlpha,

            RecNo() as AsciiNum

            autogenerate 255

            Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;

             

            Table:

            Load

            autonumber(TransID & TransLineID) as Id,

            year(floor(today() + rand()*1000)) as YearFrom,

            year(floor(today() + 1000 + rand()*4000)) as YearTo,

            TransLineID,

            TransID,

            mod(TransID,26)+1 as Num,

            Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,

            Round(1000*Rand()*Rand()*Rand1) as Expression1

              ;

            Load

            Rand() as Rand1,

            IterNo() as TransLineID,

            RecNo() as TransID

            Autogenerate 2000000

            While Rand()<=0.5 or IterNo()=1;

             

            STORE Table into Table.qvd (qvd);

             

            YearTable:

            NoConcatenate load

            Id,

            YearFrom + IterNo() -1 as Year

            From Table.qvd (qvd)

            While IterNo()-1 + YearFrom <= YearTo;

             

             

             

            Characters << AUTOGENERATE(26) 26 lines fetched

            ASCII << AUTOGENERATE(255) 191 lines fetched

            Table << AUTOGENERATE(2000000) 4.003.506 lines fetched

            YearTable << Table 31.376.589 lines fetched