I Think This will be helps u go through this
load * from D:\EMP_inc.QVD(qvd);
load * Resident EMP_Unsort order by HireDate;
let MaxDate= peek('HireDate',-1,'EMP_Sort');
drop table EMP_Unsort;
select * from MSBI.DBO.Employee
load * from D:\EMP_inc.QVD(qvd)
where not Exists(employee_id);
inner join (EMP_Load)
select * from MSBI.DBO.Employee;
store EMP_Load into D:\EMP_inc.QVD(qvd)
An incremental load use case may be implemented with Expressor but exactly how you would set up such a dataflow depends on exactly what your use case requires (for example: insert only; insert and update; insert, update and delete) and how your source data identifies deleted records (generally by using a 'current' or 'deprecated' column to indicate whether a record is still valid).
It is true that the Read Table operator does not have a property that would allow you to define a WHERE clause, and the SQL Query operator, which supports a WHERE clause, does not have a property that would allow runtime changes to the WHERE clause, but these behaviors do not prevent you from developing the incremental load use case.
The most easily implemented approach is to include in the same database as your source data another table that stores the date and time of the most recent incremental load. Then using the SQL Query operator your SELECT statement can include a WHERE clause that retrieves this value through a nested SELECT statement. Something like: SELECT * FROM ... WHERE mostRecentIncrementaloadDate > (SELECT mostRecentIncrementalLoadDate FROM ...).
Another approach is to use the Read Custom operator and DSSQL datascript module that is included with Expressor. With this approach you can store the date and time of the last incremental load as an Expressor persistent value and your code can retrieve this value and incorporate it into the SELECT statement run against your data source.
Once the SELECT statement has extracted new, updated, and deleted records from the source data, the dataflow simply uses two Join operators to differentiate between the new, updated, and deleted records, as shown in the following figure.
- The Filter operator uses the value of the 'current' or 'deprecated' column to separate new and updated records from deleted records.
- The New & Updated Join operator performs an inner join between the existing records and the new and updated records. Updated records engage in a successful join and are emitted from the output port on the right side of the operator. New records are emitted from the right port on the bottom edge of the operator. All other records are emitted from the left port on the bottom edge of the operator and are subjected to an inner join with the deleted records in the Deleted Employees Join operator.
- Those records that engage in a successful join represent deleted records, are emitted from the output port on the operator's right side, and are discarded.
- Records emitted from the left port on the bottom edge of the operator represent records that were neither deleted nor updated.
- The new, updated, and untouched records are then combined by the Funnel operator and written to a new output file.
Note that the Read Salesforce operator does include a Filter property that would allow you to use a parameter or configuration value to change at runtime the records retrieved from the data source.