topic QlikView SQL Incremental load based on Time in QlikView

QlikView SQL Incremental load based on Time

amit_saini — Sat, 16 Nov 2024 20:15:33 GMT

Dear All,

I’m working with an SQL DB , trying to do incremental load base on field called “Time” , but looks like logic is not working.

Initial load took around 1:30 HRs and even incremental taking almost same time.

Script:

Let vQVDCurrentYear = 'D:\qvdev\IT\Innovation SQL';
Let vStart = now();
Let vReloadStart = now();

if filesize('$(vQVDCurrentYear)\IOT.qvd') >0 then

  trace >>>>>> $(vQVDCurrentYear)\IOT.qvd    ;

MAX_TIME:
  load
     max(Time) as LAST_LOAD_TIME
from $(vQVDCurrentYear)\IOT.qvd (qvd);

  let vLAST_LOAD_TIME = timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'));

trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);

  drop table MAX_TIME;

else

LET vLAST_LOAD_TIME = '$(vStartDate)';
trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);

end if

// load new values
IOT:
LOAD
       *;
SQL SELECT *
FROM kauiotprodeventdb.iot.ProcessEvent
WHERE Time > '$(vLAST_LOAD_TIME)'
AND AssetSignalTag LIKE 'DB2601,REAL%'
OR AssetSignalTag LIKE 'DB2601,STRING456%'
OR AssetSignalTag LIKE 'DB2801,W%';

// load prior values
if filesize('$(vQVDCurrentYear)\IOT.qvd') >0 then

concatenate(IOT)
load
*
from $(vQVDCurrentYear)\IOT.qvd (qvd);

end if

// store all values
store IOT into $(vQVDCurrentYear)\IOT.qvd;
drop table IOT;

Does anyone has an idea what's wrong here???

Do we have any method to achieve this???

Regards,

Re: QlikView SQL Incremental load based on Time

amit_saini — Fri, 16 Aug 2019 06:42:23 GMT

Any suggestion Guys??

Regards,

Re: QlikView SQL Incremental load based on Time

marcus_sommer — Fri, 16 Aug 2019 15:28:51 GMT

I suggest to repeat it from the beginning and to look within the document-log which part needs how long. Also monitoring the workload of your machine during the execution may give hints to any bottlenecks and/or parallel running tasks.

Beside this your incremental logic on the timestamp might not be working because there are OR conditions within the where-clause - I assume you missed here the appropriate brackets. A check to the number of records would be useful, too.

Another point is your max(Time) generating which could be moved from the start to the end (before dropping IOT) and could be optimized by reading it from the symbol-table and not from the data-table: fastest-method-to-read-maxfield-from-a-qvd.

A bit contrary to the last suggestion is not to load the timestamp else splitting it into a date- and a time-field. This will require more efforts to create your where-timestamp again but it may reduce the size of your qvd quite significantely which leads to shorter store-and load-times to the filesystem and may increase the performance overall: The-Importance-Of-Being-Distinct

- Marcus