Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
Master III
Master III

QlikView SQL Incremental load based on Time

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

Or 

Do we have any method to achieve this???

Regards,

AS

Labels (2)
2 Replies
amit_saini
Master III
Master III
Author

Any suggestion Guys??

Regards,

AS

marcus_sommer
MVP & Luminary
MVP & Luminary

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