incremental load case

[ioannagr]

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.

[jwjackso]

Look at the Where Not Exists function

[chriscammers]

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;

[rwunderlich]

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

[chriscammers]

@rwunderlich fair, I was being a little lazy in my example.

[ioannagr]

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)

[rwunderlich]

Perhaps create an update field in the database table, such as  SQL Server rowversion or  Oracle SCN. What database product are you using?

-Rob