topic Looping with select and store in QlikView

Looping with select and store

Wed, 29 Jun 2011 16:45:39 GMT

Hi ,

I'm writing Qv scripting to fetch data from a huge table.Since the tabel is huge and I have to fetch data for around 3 years , I'm using looping technic to fetch data for each month and store it in qvd like incrmental load. below is the sample script I'm using.

I have verified query it works fine in DB. But, some how the QV script is failing to execute. Please advice.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO DB;

//concatenate

Let StartDate = DAYNAME('1/1/2008');

Let limitDate = addmonths ($(StartDate),1);

Do WHILE ( $(limitDate) < Today([0]))

temp:

SELECT

COL1,

COL2...

FROM

TABLE A

WHERE

and A.datecol >= $(StartDate)

and A.datecol < $(limitOASDate) ;

Concatenate

LOAD

From

D:\temp\temp.qvd

(qvd);

STORE temp intoD:\temp\temp.qvd

;

drop table temp;

let StartDate= $(limitOASDate);

let limitDate = addmonths( $(limitDate) , 1);

Loop

;

Looping with select and store

rbecher — Wed, 29 Jun 2011 17:06:16 GMT

I don't know why your are doing this, I see no benefit, but there are some typos:

Today([0]) >> today(0)

STORE temp intoD:\... >> STORE temp into D:\..

Btw, where the limitOASDate is defined?

- Ralf

Looping with select and store

Wed, 29 Jun 2011 17:32:33 GMT

Please check the updated code: I'm doing as the source table is huge and query is getting stuck to fetch 3 years worth data in single shot. hence, I'm splitting it into monhs and trying to do a incremental load.

Let me know if this logic supported by QV.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO DB;

//concatenate

Let StartDate = DAYNAME('1/1/2008');

Let limitDate = addmonths ($(StartDate),1);

Do WHILE ( $(limitDate) < Today([0]))

temp:

SELECT

COL1,

COL2...

FROM

TABLE A

WHERE

and A.datecol >= $(StartDate)

and A.datecol < $(limitDate) ;

Concatenate

LOAD

From D:\temp\temp.qvd

(qvd);

STORE temp into D:\temp\temp.qvd ;

drop table temp;

let StartDate= $(limitDate);

let limitDate = addmonths( $(limitDate) , 1);

Loop

;

Looping with select and store

rbecher — Wed, 29 Jun 2011 17:55:13 GMT

Why not just w/o the Store?

Do While

temp:

ADD SQL SELECT.....

Where...;

Loop;