Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Learn it from qlik help: Using QVD files for incremental load ‒ QlikView which requires no wait.
Check this: Incremental loading - All scenarios
Its asks me to join a group.. just requested .
thanks
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.
Learn it from qlik help: Using QVD files for incremental load ‒ QlikView which requires no wait.
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 ------