Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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?
BR
Dan
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
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
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
ENDIF