topic Re: Issues in incremental load using not exsists() in QlikView

Issues in incremental load using not exsists()

aniruddhyadutta — Wed, 25 Nov 2020 16:16:04 GMT

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()?

Re: Issues in incremental load using not exsists()

swuehl — Thu, 16 Mar 2017 13:03:31 GMT

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.

Re: Issues in incremental load using not exsists()

aniruddhyadutta — Thu, 16 Mar 2017 13:07:37 GMT

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.

Re: Issues in incremental load using not exsists()

swuehl — Thu, 16 Mar 2017 13:14:28 GMT

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.

Re: Issues in incremental load using not exsists()

german24 — Thu, 16 Mar 2017 13:31:26 GMT

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