Issues in incremental load using not exsists()

[aniruddhyadutta]

Hi all,

I am doing a full load.let say the load has 100 records.

now I am running an incremental load

flow

====

1.getting max date from the full load qvd

2.Getting records from data base where date> last date in history qvd.

3. concatenating the above delta records sets with the history QVD where not exists(ID which is primary key)

now the issue is just after full load when I am running the incremental app  the delta part from data base is fetching 0 records.

but when its getting concated with history QVD I am seeing less record in the final data set..(i.e 90 instead of 100)

is it due to I am using not exists()?

[swuehl]

Are you sure your ID is a primary key? If it's not, NOT EXISTS() will not fetch any IDs that are part of the LOADed records so far.

[aniruddhyadutta]

Hi Stefan,

Thanks for your time.

But I can see records loading from the history QVD being fetched in the incremental app.

Only issues is even if the delta part is 0,in my final data set after concatenating with the history qvd I am getting less than the original full load.

[swuehl · Accepted solution]

Ok, I understood that.

I assume ID is not a primary key of your table, instead you can create new records with same ID and new Timestamp, so only the combination will be unique.

I assume you want to implement an Update process, so historic records with older timestamp will be replaced with new records. Your initial full load should handle that, e.g. by only loading the IDs with latest timestamp.

I assume that your current process will only show the oldest records per ID.

[german24]

Hi,

try this...

TableMax_Date:

LOAD

  Max(Date) AS Max_Date

FROM QVD_history.qvd (qvd);

LET vMax_Date = Peek('Max_Date', 0, 'TableMax_Date');

DROP TABLE TableMax_Date;

QVD_incremental:

SELECT

  *,

  ID AS id_exists

FROM DATABASE

WHERE Date > '$(vMax_Date)'

;

IF not isnull(QvdCreateTime('QVD_history.qvd')) THEN

  CONCATENATE(QVD_incremental)

  LOAD *

  FROM QVD_history.qvd (qvd)

  WHERE not exists(id_exists,ID);

END IF

DROP FIELD id_exists;

STORE QVD_history INTO QVD_history.qvd (qvd);

DROP TABLE QVD_history;

Best regards,

German