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

Create a table from date ranges.

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.

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.

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:

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:

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

;

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:

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

• Re: Create a table from date ranges.

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