on which i want to do incremental load. First, i though by id, but they aren't sequential, meaning the biggest id isn't the most recent one, so creating a variable and do "where id>'$(MaxID';" doesn't serve me.
Secondly I tried with LastDate, but the thing is the LastDate is a test one coming from 2036, so no new data is found always.
What should i do? I thought about Filetime() on the initial qvd, what are your ideas?
Your example has some pretty tough limitations, without a specific way to identify changes in the source data it is very difficult to implement an incremental load process. The best you can hope for is comparing a previously loaded qvd file to one that is more recently loaded and then creating flags for new and deleted records.
I would load the previous qvd and then do a full outer join of the new qvd
Load id, date, 1 as inOldFile
//full outer join based on only the id field
Load id, date as NewDate, 1 as inNewFile
//new table to calculate some important measures related to file comparison
If(isnull(NewDate),date,NewDate) as date
If(Isnull(inNewFile),1,0) as isDeleted,
If(Isnull(inOldFile),1,0) as isNewRecord,
If(not isnull(inOldFile) and Not isnull(inNewFile) and date <> NewDate,1,0) as isChanged