Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
The followings are what I am trying to do.
(1) Test_Data_qvf:Load "Position_Historical" data as of 2017/5/2 from "Test_DB.accdb" into "Position_Historical_QVD".
(2) Test_Data_qvf:Load Incremental "Position_Historical" data as of 2017/5/3 from "Test_DB.accdb" into "Position_Historical_QVD".
(3) Test_Structure_qvf:Load "Position_Historical" data from "Position_Historical_QVD".
If I run (1) and (3), the data can be loaded perfectly.
However, if I run (1), (2) and (3), the data as of 2017/5/3 cannot be loaded, and data as of 2017/5/2 is also deleted.
Could anyone advise what it's wrong in the following script?
===== [Script in Test_Data_qvf] =====
LIB CONNECT TO 'Test_DB';
//(1)Load Data as of 2017/5/2 to QVD
LET DataDate = Date(Date#('2017/5/2','YYYY/MM/DD'),'MM/DD/YYYY');
LOAD `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`;
SQL SELECT `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`
FROM `Position_Historical`
WHERE Report_Date=#$(DataDate)#;
STORE Position_Historical INTO [lib://Test_QVD/Position_Historical.QVD](QVD);
//(2)Load Incremental Data as of 2017/5/3 to QVD
LET DataDate = Date(Date#('2017/5/3','YYYY/MM/DD'),'MM/DD/YYYY');
QV_Table:
LOAD `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`;
SQL SELECT `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`
FROM `Position_Historical`
WHERE Report_Date=#$(DataDate)#;
NoConcatenate LOAD
`Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`
FROM [lib://Test_QVD/Position_Historical.QVD](QVD)
WHERE NOT EXISTS(Report_Date);
STORE QV_Table INTO [lib://Test_QVD/Position_Historical.QVD](QVD);
DROP TABLE QV_Table;
===========================================================
Hi Rex !
I have updated your script plz check
===== [Script in Test_Data_qvf] =====
LIB CONNECT TO 'Test_DB';
Record:
LOAD `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`;
SQL SELECT `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`
FROM `Position_Historical`;
STORE Position_Historical INTO [lib://Test_QVD/Position_Historical.QVD](QVD);
update_Record:
LOAD*
Resident Record
Order by Report_Date;
LET vUpdated_DateHeader= Peek('Report_Date',-1,'update_Record');
DROP Table Record;
LIB CONNECT TO 'Test_DB';
test:
LOAD `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`;
Where Report_Date>='$(vUpdated_DateHeader)';
SQL SELECT `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`
FROM `Position_Historical`;
Concatenate
LOAD `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`;
FROM [lib://Test_QVD/Position_Historical.QVD]
(qvd)
Where not Exists (Client_ID);
If you want to change the dates you can easily pass the dates in variable and use.
LET vUpdated_DateHeader= Peek('Date(Date#('2017/5/3','YYYY/MM/DD'),'MM/DD/YYYY');',-1,'update_Record');
but plz confirm that the format of the variable should be same as the Report Date field.
Dear Sanjay,
May I have few more questions:
(1) Shouldn't I add the line below at the bottom of script you modified since the incremental data was not added in QVD?
STORE test INTO [lib://Test_QVD/Position_Historical.QVD](QVD);
(2) If I add the line in script, QVD would only have data as of 'vUpdated_DateHeader', and data before 'vUpdated_DateHeader' seems be deleted?
===== [script you modified] =====
test:
LOAD `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`;
Where Report_Date>='$(vUpdated_DateHeader)';
SQL SELECT `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`
FROM `Position_Historical`;
Concatenate
LOAD `Client_ID`,
`Loan_USD`,
`Market_Value_USD`,
`Report_Date`,
`Security_ID_BLG`;
FROM [lib://Test_QVD/Position_Historical.QVD]
(qvd)
Where not Exists (Client_ID);
==========================================
Ans 1 : i forgot to mention the below line you can add no issue with tha.
Ans 2: No it will not be deleted it just got concat with the previously data available.
Dear Sanjay,
I will check the script again since the past data always be deleted on my side when I run incremental add.