Create a table from date ranges.

[#1]

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

Key	Year
key	2003
key	2004
...	...
key	2012

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.

[Gysbert_Wassenaar · Accepted solution]

Typically it's done with something like this:

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

FROM MyTableWith32MRecords.qvd (qvd)

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

[maxgro]

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

[#4]

My tries were heavy. Gysbert Idea is running and looks a lot faster than mine...