topic Re: append, or incremental load for QVD in QlikView

append, or incremental load for QVD

Anonymous — Wed, 11 Jul 2012 06:21:03 GMT

I am trying to understand use of QVD.

I have created a QVD with this code:

let vCIPStepQVD = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-01-01 00:00:00' & chr(39) & ',' & '@EndTime = ' & chr(39) & '2012-07-09 23:59:59' & chr(39);

CIPStepQVD:

LOAD * ;

SQL $(vCIPStepQVD);

Store CIPStepQVD INTO ;

Now I need to add data on an hourly time schedule, and wonder if this will work:

let vCIPStepQVD = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & reloadtime & chr(39) & ',' & '@EndTime = ' & chr(39) & Now() & chr(39);

CIPStepQVD:

LOAD * ;

SQL $(vCIPStepQVD);

Store CIPStepQVD INTO ;

The idea is that it should use last reloadtime() as start point each time and have Now() as end point.

But I am not familiar with QVD. Would this code append the current data to the original QVD or will it ereas it and replace it with the new data?

Dan

Re: append, or incremental load for QVD

somenathroy — Wed, 11 Jul 2012 06:59:47 GMT

Hi,

Store CIPStepQVD INTO ;

The above command overwrites the existing Data.qvd file.

Thus before executing the SQL, you need to check whether the this initial ( first time) execution or incremental execution.

You can check this by NoOfRows('[Table Name]') function after LOADing ..\Data.qvd file.

If INITIAL then

CIPStepQVD:

LOAD * ;

SQL $(vCIPStepQVD);

Store CIPStepQVD INTO ;

else

Concatenate(CIPStepQVD)

LOAD * ;

SQL $(vCIPStepQVD);

Store CIPStepQVD INTO ;

regards,

Som

Re: append, or incremental load for QVD

Anonymous — Wed, 11 Jul 2012 08:28:09 GMT

Hi Som

Thanks for reply. I am trying to understand what you wanted me to do.

I have created a new app and here is what I thought you asked me to test:

**************

let vCIPStepQVD1 = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-07-10 00:00:01' & chr(39) & ',' & '@EndTime = ' & chr(39) & '2012-07-10 06:00:00' & chr(39);

let vCIPStepQVD2 = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-07-10 06:00:01' & chr(39) & ',' & '@EndTime = ' & chr(39) & '2012-07-10 07:00:00' & chr(39);

//created vCIPStepQVD1 to be the intial load code. vCIPStepQVD2 is the increment code.

//Use smaler timespann otherwise it would take for ever to load.

FaseHistory:

LOAD*

FROM

(qvd);

LET NoOfRows =noofrows('FaseHistory');

IF NoOfRows >= 1 THEN

CIPStepQVD1:

LOAD

//I insert a loadtime field as to tell at waht time it was loaded.

'2012-07-10 06:00:00' as LoadTime,*;

SQL $(vCIPStepQVD1);

Store CIPStepQVD1 INTO ;

ELSEIF

Concatenate(CIPStepQVD2)

LOAD

'2012-07-10 07:00:00' as LoadTime,*;

SQL $(vCIPStepQVD2);

Store CIPStepQVD2 INTO ;

ENDIF

**************

But I get the following error:

Table not found

Store CIPStepQVD1 INTO

It seem to be something with the concatenate part of the code.

Regards

Dan

Re: append, or incremental load for QVD

somenathroy — Wed, 11 Jul 2012 16:04:35 GMT

Pls. try the below code:

let vCIPStepQVD1 = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-07-10 00:00:01' & chr(39) & ',' & '@EndTime = ' & chr(39) & '2012-07-10 06:00:00' & chr(39);

let vCIPStepQVD2 = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-07-10 06:00:01' & chr(39) & ',' & '@EndTime = ' & chr(39) & '2012-07-10 07:00:00' & chr(39);

//created vCIPStepQVD1 to be the intial load code. vCIPStepQVD2 is the increment code.

//Use smaler timespann otherwise it would take for ever to load.

FaseHistory:

LOAD*

FROM

(qvd);

LET NoOfRows =noofrows('FaseHistory');

IF NoOfRows < 1 THEN

CIPStepQVD1:

LOAD

//I insert a loadtime field as to tell at waht time it was loaded.

'2012-07-10 06:00:00' as LoadTime,*;

SQL $(vCIPStepQVD1);

Store CIPStepQVD1 INTO ;

ELSEIF

Concatenate(FaseHistory) //Changed line

LOAD

'2012-07-10 07:00:00' as LoadTime,*;

SQL $(vCIPStepQVD2);

Store FaseHistory INTO ; //Changed line

ENDIF