Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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;


talk is cheap, supply exceeds demand
3 Replies

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;


talk is cheap, supply exceeds demand
MVP
MVP

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:

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

Re: Create a table from date ranges.

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

Community Browser