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

    Create a table from date ranges.

    Gonzalo Bianchi



      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




      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?



        • 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?






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




            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 ;




            autonumber(TransID & TransLineID) as Id,

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

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



            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



            Rand() as Rand1,

            IterNo() as TransLineID,

            RecNo() as TransID

            Autogenerate 2000000

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


            STORE Table into Table.qvd (qvd);



            NoConcatenate load


            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