Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is there a way to load & concatenate multiple QVD files and do an incremental load on only one of the QVD files
I have a 2015.qvd, a 2016.qvd file, and a 2017.qvd file. The 2015 & 2016 are static, but the 2017 needs to be updated daily. With the code below when I STORE the 2017, it duplicates the data from 2015 & 2016 in the 2017 file.
[Stats]:
LOAD *
from 'lib://QVD (folder)/Stats_2015.qvd' (qvd);
concatenate
LOAD *
from 'lib://QVD (folder)/Stats_2016.qvd' (qvd);
concatenate
LOAD *
from 'lib://QVD (folder)/Stats_2017.qvd' (qvd);
CONCATENATE
LIB CONNECT TO 'DBConnection';
LOAD .......;
SQL *
FROM Data.dbo.Stats
where convert(int,Startdate) > $(Last_Update_Date);
Last_Update_Date:
LOAD Max(StartDate) as MaxDate
Resident [Stats];
Let Last_Update_Date=Peek('MaxDate',0,'Last_Update_Date');
Store Stats into 'lib://QVD (folder)/Stats_2017.qvd';
Try to create single qvd of 2015,2016 as you say they are static then you need to just concatenate this qvd at the last of the incremental load. And incremental load takes place only in the 2017 file with mas date logic.
//MAX DATE FINDING
Last_Update_Date:
LOAD Max(StartDate) as MaxDate
from 'lib://QVD (folder)/Stats_2017.qvd' (qvd);
Let Last_Update_Date=Peek('MaxDate',0,'Last_Update_Date');
//INCREMENTAL LOAD START
LIB CONNECT TO 'DBConnection';
[Stats_2017]
LOAD .......;
SQL *
FROM Data.dbo.Stats
where convert(int,Startdate) > $(Last_Update_Date);
concatenate(Stats_2017)
LOAD *
from 'lib://QVD (folder)/Stats_2017.qvd' (qvd);
Store Stats into 'lib://QVD (folder)/Stats_2017.qvd';
Drop table Stats_2017;
[Stats]
LOAD *
from 'lib://QVD (folder)/Stats_2017.qvd' (qvd);
concatenate(Stats)
LOAD *
from 'lib://QVD (folder)/Stats_2015.qvd' (qvd);
concatenate(Stats)
LOAD *
from 'lib://QVD (folder)/Stats_2016.qvd' (qvd);