We are having ShopFloor reports , which we are triggering every 3 hours from QMC , but sometimes during QVD Generation we are missing data. Let me explain this with an example : Suppose we have triggered task from QMC at 6:00 AM on Monday, but at that time because of XYZ reasons data was not available and this leads to no data inside report for Monday ,later on Tuesday data got updated for Monday in DB , but as we are generating data based on last reload time (Incremental load) ,we don't have possibilities to see Monday's missing data on Tuesday , because each trigger will load new records and will not take care of missing records.
In General we are using below script :
LET vStartDate = date(makedate(2016,1), 'DD.MM.YYYY hh:mm:ss');
Set vQVD = '\\TXKAPPAZU025\d$\qvprod\QV_QVD';
Let vQVDCurrentYear = '\\TXKAPPAZU025\d$\qvprod\QV_QVD\'& year(today());
Let vStart = now();
Let vReloadStart = now();
///$tab STICHPROBE
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd) ;
let vLAST_LOAD_TIME = timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');
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
STICHPROBE:
LOAD
'$(vPlant)' as Plant,
*;
SQL SELECT
NSPCIDNR,
NTOOLNR,
DTTSPROBE,
DTTSERF,
NSTPFEHLERFLAG,
DXMIN,
DTO,
DTU,
DXQ
FROM $(vPlantAutor).STICHPROBE
WHERE DTTSPROBE > to_timestamp('$(vLAST_LOAD_TIME)', 'DD.MM.YYYY HH24:MI:SS');
// load prior values
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
concatenate(STICHPROBE)
load
*
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd);
end if
// store all values
store STICHPROBE into $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd;
LET vSTICHPROBE_LOAD_DURATION = Interval(now() - vStart) ;
LET vStart = now();
LET vSTICHPROBE_Size = num(filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd')/1024,'00.0') & ' KB';
LET vSTICHPROBE_Records = QvdNoOfRecords ('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd');
drop table STICHPROBE;
To overcome this issue what we thought of re-generating QVDs , so on every 5th day of Week .i.e Friday we will be doing full load from Monday of same week so that we can again recall missing records during whole weeks in QVDs on Friday. To achieve this I'm trying below changes in above script , but looks like this is not working 100%.
*****New Changes are in red
LET vStartDate = date(makedate(2016,1), 'DD.MM.YYYY hh:mm:ss');
Set vQVD = '\\TXKAPPAZU025\d$\qvprod\QV_QVD';
Let vQVDCurrentYear = '\\TXKAPPAZU025\d$\qvprod\QV_QVD\'& year(today());
Let vLoadFrom = timestamp(If(Weekday($(vLAST_LOAD_TIME)) >= 5, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD.MM.YYYY HH24:MI:SS');
Let vStart = now();
Let vReloadStart = now();
///$tab STICHPROBE
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
