topic Re: Update incremental in QlikView

Update incremental

Fri, 07 Dec 2012 08:47:42 GMT

In insert and update method of incremental load,

For inserting new records we use TimeModified filed.

Can I use any of the Time fieldd for updating records. Or I must use primary key to update.

Re: Update incremental

Anonymous — Fri, 07 Dec 2012 08:54:56 GMT

Sure, you can use the TimeModified for updates. Just load all records from source where the TimeModified is larger than last run. Now only load records from the QVD file where the transaction IDs don't exist in the source table. Concatenate and store to QVD. Done.

For deleting records, you'll need to use the ID to parse out which records have been removed since last updating the QVD.

Re: Update incremental

Fri, 07 Dec 2012 09:00:45 GMT

Max:

LOAD

DATIME

FROM E:\Qlikview\Data\RawQVD\abc_INC.qvd

(qvd);

Res:

Load DATIME as ModDate

Resident Max order by DATIME Desc;

Let MaxTimeStamp=Peek('ModDate',0);

Let MinDate=Peek('ModDate',-1);

Let MaxTIMESTAMP=Timestamp($(#MaxTimeStamp),'YYYY-MM-DD-hh.mm.ss.ffffff');

drop table Max,Res;

abc_INC:

LOAD

ZLOTPFX,

ZLOTCOY,

ZSBUCODE,

ZLOTNUM, //Key (This field is unique)

DATIME;

SQL SELECT *

FROM abc

WHERE DATIME >= '$(MaxTIMESTAMP)';

CONCATENATE

LOAD ZLOTPFX,

ZLOTCOY,

ZSBUCODE,

ZLOTNUM,

DATIME

FROM E:\Qlikview\Data\RawQVD\abc_INC.qvd

(qvd) WHERE NOT EXISTS(ZLOTNUM);

Store abc_INC into ;

Drop Table abc_INC;

I am just taken this from community as some one's example only.

Here we using at last where not exists. This is functioning as update. And DATIME functioning as insert.

Let me know your thought.

Re: Update incremental

Tue, 18 Dec 2012 14:00:10 GMT

Thanks for this as I am trying to implement something similar to mine. One question about the following line:

FROM abc

Where is abc? Should this be abc_inc or should be the file path?

Thanks.

Re: Update incremental

Tue, 18 Dec 2012 14:16:13 GMT

abc_INC is the initial QVD. This is not the path.

Re: Update incremental

Tue, 18 Dec 2012 14:18:52 GMT

I think u asling abc but not abc_INC.If so then abc is the database table from which we taking incremental values and concatenationg with existing qvd i.e abc_INC.

Re: Update incremental

Tue, 18 Dec 2012 14:48:42 GMT

Thanks for your reply. I am indeed asking where 'abc' is. I can't find it defined anywhere, and I am doing something wrong where the following code doesn't work.

Do you know what I am doing wrong? This is the first attempt where I am trying to read off a raw data excel file and compare to qvd, then put new records into qvd. Thanks again.

Max:

LOAD Run_Time

FROM

(qvd);

Res:

Load Run_Time as ModDate

Resident Max order by Run_Time Desc;

Let MaxTimeStamp=Peek('ModDate',0);

Let MinDate=Peek('ModDate',-1);

Let MaxTIMESTAMP=Timestamp($(#MaxTimeStamp),'YYYY-MM-DD-hh.mm.ss.ffffff');

drop table Max;

drop table Res;

Appended_Data:

LOAD Name,

Location,

Date,

[# Entry],

Revenue,

Run_Time

FROM

(ooxml, embedded labels, table is Sheet1)

where Run_Time >= $(MaxTIMESTAMP);

CONCATENATE

LOAD Name,

Location,

Date,

[# Entry],

Revenue,

Run_Time

FROM

(qvd);

STORE Appended_Data into C:\Users\212071760\Desktop\QlikView Tests\QVD Testing\QVD_Test.qvd

Re: Update incremental

Tue, 18 Dec 2012 15:03:46 GMT

In the script posted by me above not shown the path. Abc is the database table but not specified the path. We need to make an assumption that path exists with abc as it is sample code. Its something like “from dbo.qlikview.abc”. Since you using excel so you will have some path c:\.... something like this.

Can you tell me what error u getting. Share the screenshot of error.

Re: Update incremental

Tue, 18 Dec 2012 15:13:35 GMT

LET vLastExecTime = TIMESTAMP(0, 'YYYY-MM-DD hh:mm:ss.fff'); // resetting vLastExecTime

LoadTime:

LOAD MAX(TimeModified) AS X

FROM D:\TW Apps\Incremental\W6TASKS.qvd (qvd);

LET vLastExecTime =TIMESTAMP(PEEK('X',0,'LoadTime'),'YYYY-MM-DD hh:mm:ss.fff');

DROP TABLE LoadTime;

LOAD *;

SQL SELECT * FROM "QLKVIEW_SO".dbo.W6TASKS WHERE TimeModified > ' $(vLastExecTime) ';

CONCATENATE ()

LOAD *

FROM D:\TW Apps\Incremental\W6TASKS.qvd (qvd);

STORE INTO D:\TW Apps\Incremental\W6TASKS.qvd;

Use this one which is free from error. Hope it helps. Instead of DB table you can use excel above as per your requirement.

Re: Update incremental

Tue, 18 Dec 2012 15:21:51 GMT

Ok that makes sense. I thought it was the path of the data file that will be appended to the qvd but wasn't sure. Thanks.