topic Re: Incremental load help for QVD Gen in QlikView

Incremental load help for QVD Gen

amit_saini — Thu, 19 May 2016 03:59:14 GMT

Hi Folks,

Attached is our QVD Gen from one of our support team , which is some how complicated (Use of variables).

I have tried few things in order to do incremental load on generated qvd's , but failed to do so.

Currently I believe this is doing full load in each trigger and storing qvd's. See below:

I'm looking for a solution where I can do incremental load on generated qvds, can't wait for 2 hours for new data.

I tried so many things based on comments from developer at script side , but still no success .

Please suggest what is the best possible way for incremental load.

Thanks,

Re: Incremental load help for QVD Gen

amit_saini — Thu, 19 May 2016 04:59:03 GMT

any suggestions???

Thanks,

Re: Incremental load help for QVD Gen

amit_saini — Thu, 19 May 2016 06:33:21 GMT

kush141087‌

jagan‌

Please help!

Thanks,

Re: Incremental load help for QVD Gen

stabben23 — Thu, 19 May 2016 06:41:36 GMT

Hi Amit, Your script looks OK, havent look so deep into it but should work. The most important is that DTUPDATE and Last_Updated_Date is in exactly same format otherwise it will do a full reload.

Re: Incremental load help for QVD Gen

Anonymous — Thu, 19 May 2016 06:48:36 GMT

Save on another QVD the last date. Filter new data with this date and load the last QVD filtering date. Store all table on the new QVD.

Kind regards

Re: Incremental load help for QVD Gen

amit_saini — Thu, 19 May 2016 06:59:43 GMT

Staffan,

Thanks for the input!

I have asked for the DTUPDATE format to database admin.

Thanks,

Re: Incremental load help for QVD Gen

Kushal_Chawda — Thu, 19 May 2016 07:05:33 GMT

Hi Bro,

I don't have QlikView in my system to look into your application, but can you just give me an overview what issue you are facing currently?

Re: Incremental load help for QVD Gen

amit_saini — Thu, 19 May 2016 07:10:04 GMT

Hi Kush,

Attached is script for qvd generator , the issue is every time this is doing full load , I want to do Incremental load .

Thanks,

Re: Incremental load help for QVD Gen

Kushal_Chawda — Thu, 19 May 2016 07:17:06 GMT

Can you tell me what is the value you are getting in variable "Last_Updated_Date"?

Re: Incremental load help for QVD Gen

amit_saini — Thu, 19 May 2016 07:26:20 GMT

Sorry but can't see this variable in list

But LastExecTime is having below value:

Thanks,

Re: Incremental load help for QVD Gen

Kushal_Chawda — Thu, 19 May 2016 07:30:28 GMT

Are you sure that, DTUPDATE field format in database is DD.MM.YYYY hh;mm:ss format ? Can you confirm that it is not the String value in database?

or try to_timestamp() instead to_date()

to_timestamp('$(Last_Updated_Date)', 'dd.mm.yyyy HH24:MI:SS')

Re: Incremental load help for QVD Gen

amit_saini — Thu, 19 May 2016 07:34:02 GMT

Yes I'm checking for format , will let u knw!

Thanks,

Re: Incremental load help for QVD Gen

stabben23 — Thu, 19 May 2016 07:34:52 GMT

If you dont have "Last_Updated_Date then this statment wont work:

WHERE DTUPDATE > to_date('$(Last_Updated_Date)', 'dd.mm.yyyy HH24:MI:SS')

I'm also wondering what "to_date" is?

WHERE DTUPDATE > '$(Last_Updated_Date)' should be enough.

I should also do my dateformat Within the variable instead of in where statment.

Re: Incremental load help for QVD Gen

stabben23 — Thu, 19 May 2016 08:00:34 GMT

incrementalLoadMode=0 in Your script. Cant find where you set this variable.

// incrementalLoadMode: 1 = only insert 2 = insert and update 3 = insert, update and delete

is what is comment in Your script

Re: Incremental load help for QVD Gen

amit_saini — Thu, 19 May 2016 08:03:23 GMT

Yes I have already tried based on comment part, but still full load.

Thanks,.

Re: Incremental load help for QVD Gen

jagan — Thu, 19 May 2016 08:10:25 GMT

Hi,

Date format is the issue, check it correctly.

Regards,

Jagan.

Re: Incremental load help for QVD Gen

rwunderlich — Thu, 19 May 2016 15:50:03 GMT

Can you post the Document log?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Incremental load help for QVD Gen

amit_saini — Fri, 20 May 2016 10:56:52 GMT

Hi Rob,

First of all was pleasure meeting you @ Qonnections2016.

Attached is the log file

Also sharing one of our pic

Thanks,

Re: Incremental load help for QVD Gen

amit_saini — Fri, 20 May 2016 10:57:59 GMT

Thanks

Re: Incremental load help for QVD Gen

stabben23 — Fri, 20 May 2016 11:36:05 GMT

20.05.2016 10:30:20: 0118 SQL SELECT NLFDPONR || NSTEPNR || NOPERSPLITTNR || DTTIMESTAMP AS UNIQUEKEY, PRI_EVENTS_HIST.*

FROM PRI_EVENTS_HIST

20.05.2016 10:30:21: 31 fields found: UNIQUEKEY, NLFDPONR, NSTEPNR, NOPERSPLITTNR,

DTTIMESTAMP, NEVENT, NLFDEVNR, NLFDMASCHNR, NLFDCHARGENNR,

NOPERSTATNR, NLFDSTATIONNR, NRESSOURCENR, NPERS_IDNR, NEINHEIT,

NLFDARTIKELNR, NIDNR, NMENGE, NVALUE, SVALUE, NREASONNR, NDTTIMESTAMPTZID,

NLFDCHARGENNRD, NBUNCHQUANTITY, NBUNCHUNIT, NMATSTATUSID, NWITHDRAWALID,

NRESTOOLCAVITYID, NPRIEVENTID, DTREFERENCE, NDTREFERENCETYID, NDTREFERENCETZID, 9,134,782 lines fetched

9.134.782 should not be fetched when you load incrementall, check Your timestamps here.