Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello I have a simple qvd like
Qvd:
Load id, date
from blabla.qvd;
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?
Thank you in advance.
Look at the Where Not Exists function
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
BlaBla:
Load id, date, 1 as inOldFile
from old_blabla.qvd;
//full outer join based on only the id field
Join(BlaBla)
Load id, date as NewDate, 1 as inNewFile
from new_blabla.qvd;
//new table to calculate some important measures related to file comparison
BlaBlaBla:
Load
id,
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
resident BlaBla;
It's unusual to do an incremental load from a QVD. Incremental loads are typically done from a database. Can you describe your use case?
-Rob
@rwunderlich fair, I was being a little lazy in my example.
Hi, my english is at times not so well; Forgive me.
I want to do incremental load for a qvd (if that's better).
MaxKeyLoad:
Load Max(id) as MaxID
from [$(vPathQVD)MyQvd.qvd] (qvd);
Let MaxID = Peek('MaxID', 0 , MaxKeyLoad);
Trace $(MaxID);
MyTable:
LOAD id ,
creationdate;
SQL SELECT id,
creationdate
FROM public.mytable
where id>'$(MaxID)';
concatenate
Load * from [$(vPathQVD)MyQvd.qvd] (qvd)
where not exists(id);
That was the first try which didn't give me correct results as the biggest id isn't the most recent.
Then tried with date
Last_Date:
Load timestamp(Max(date(creationdate,'DD/MM/YYYY h:mm:ss TT'))) as MaxDate
from [$(vPathQVD)MyQvd.qvd] (qvd);
Let Last_Date=timestamp(date(Peek('MaxDate',0,Last_Date)),'DD/MM/YYYY h:mm:ss TT');
Trace $(MaxDate);
MyTable:
LOAD id ,
creationdate;
SQL SELECT id,
creationdate
FROM public.mytable
where creationdate>'$(MaxDate)';
concatenate
Load * from [$(vPathQVD)MyQvd.qvd] (qvd)
where not exists(id);
this also can't work because today is 14.6 and the max date is in 2036 so no new data is loaded.
How to handle incremental load now?
@chriscammers @rwunderlich @jwjackso (also thanks for your time)
Perhaps create an update field in the database table, such as SQL Server rowversion or Oracle SCN. What database product are you using?
-Rob