Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Typically it's done with something like this:

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

FROM MyTableWith32MRecords.qvd (qvd)

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


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Typically it's done with something like this:

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

FROM MyTableWith32MRecords.qvd (qvd)

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


talk is cheap, supply exceeds demand
maxgro
MVP
MVP

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

Not applicable
Author

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