Qlik load time optimization

[Anonymous]

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

[tresesco]

Check this: Incremental loading - All scenarios

[Anonymous]

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

thanks

[jonathandienst]

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.

[tresesco · Accepted solution]

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

[Anonymous]

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,

    y,

    m,

    d,

    yw,

    w,

    q,

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