Qlik load time optimization

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

Hi all

I have a dashboard that loads a view from a mysql db. This view contais several million lines and can take some time to load .

Since this is a time table, that stores events, Is there a way to load only the new lines that were added.?

or any optimization one can do in the script ?

BTW : is there a good first steps into load script guide ?

Thank you all

Jorge

Re: Qlik load time optimization

tresB — Thu, 27 Jul 2017 08:07:15 GMT

Check this: Incremental loading - All scenarios

Re: Qlik load time optimization

Anonymous — Thu, 27 Jul 2017 08:09:24 GMT

Its asks me to join a group.. just requested .

thanks

Re: Qlik load time optimization

jonathandienst — Thu, 27 Jul 2017 08:39:18 GMT

This is called incremental loading - the previous query results are stored into, and loaded from, a qvd file and only the most recent records need to be fetched from the database. Check out incremental loading in the reference manual and/or search here for more information.

Re: Qlik load time optimization

tresB — Thu, 27 Jul 2017 09:22:11 GMT

Learn it from qlik help: Using QVD files for incremental load ‒ QlikView which requires no wait.

Re: Qlik load time optimization

Anonymous — Thu, 27 Jul 2017 15:29:49 GMT

Solved it Like this:

created a variable lastimportedid , then

load from sql where id < lastimportedid

load a qvd file with all data (if exists)

save all to qvd file

update lastimportedid

my script is as bellow

thanks all for the tips

//-----------------------------------------------

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Fev;Mar;Abr;Mai;Jun;Jul;Ago;Set;Out;Nov;Dez';

SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

SET LongMonthNames='Janeiro;Fevereiro;Março;Abril;Maio;Junho;Julho;Agosto;Setembro;Outubro;Novembro;Dezembro';

SET LongDayNames='segunda-feira;terça-feira;quarta-feira;quinta-feira;sexta-feira;sábado;domingo';

SET FirstWeekDay=0;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='pt-PT';

//-------- Start Multiple Select Statements ------

vw_nums_caller:

//load da db desde o ultimo id importado

LOAD id,

accountnumber,

origin,

destination,

calldate,

day,

month,

year,

starttime,

starthour,

duration,

cost,

calltype,

originalfilename,

originalfilehash,

originalfileid,

altertime,

`account_type`,

invoicenumber,

contractnumber,

volume,

servicetype,

timeofday,

callclass,

profile,

name,

dept,

cia;

SQL SELECT *

FROM communications.`vw_nums_caller` where id>$(lastimportedid);

//adicionar o que ja tinha sido gravado

CONCATENATE LOAD id,

accountnumber,

origin,

destination,

calldate,

day,

month,

year,

starttime,

starthour,

duration,

cost,

calltype,

originalfilename,

originalfilehash,

originalfileid,

altertime,

`account_type`,

invoicenumber,

contractnumber,

volume,

servicetype,

timeofday,

callclass,

profile,

name,

dept,

cia

FROM vw_nums_caller_data.qvd (qvd);

LOAD `idtbl_processedfiles`,

filename,

filehash;

SQL SELECT *

FROM communications.`tbl_processedfiles`;

LOAD date,

id,

yw,

wd,

`m_name`,

`wd_name`;

SQL SELECT *

FROM communications.`tbl_time_dimension`;

STORE vw_nums_caller into vw_nums_caller_data.qvd;

temp:

LOAD

max(id) as mxid

resident vw_nums_caller;

LET lastimportedid= peek('mxid');

Drop table temp;

//-------- End Multiple Select Statements ------

topic Re: Qlik load time optimization in QlikView

Qlik load time optimization

Re: Qlik load time optimization

Re: Qlik load time optimization

Re: Qlik load time optimization

Re: Qlik load time optimization

Re: Qlik load time optimization