Script help for missing records on Weekly basis

[amit_saini]

Hi Folks,

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

  trace >>>>>> $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd    ;

  MAX_TIME:

  load

     max(DTTSPROBE) as LAST_LOAD_TIME

  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

  trace >>>>>> $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd    ;

  MAX_TIME:

  load

     max(DTTSPROBE) as LAST_LOAD_TIME

  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: $(vLoadFrom);

  drop table MAX_TIME;

else

  LET vLAST_LOAD_TIME = '$(vStartDate)';

  trace >>>>>> LAST_LOAD_TIME:  $(vLoadFrom);

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('$(vLoadFrom)', '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;

Please advice if any changes required to support missing data scenario or what should be the best approach in such cases.

Thanks in advance!

AS

[amit_saini]

any suggestion???

Thanks,

AS

[Anil_Babu_Samineni]

Are you facing any problem over here? What was the issue.

[amit_saini]

Issues:

- Data in Qvds is not correct

-Generation of QVds take much longer time-

- Tasks from QMC is getting hanged

Thanks,

AS

[settu_periasamy]

Hi Amit,

If you are getting failed that qvd generator on monday, may be you can try the 'ScriptErrorCount' Variable.

Like

If ScriptErrorCount = 0 then

  Let vLAST_LOAD_TIME= date(now()-1);

End if

[sasikanth]

HI,

I have Question Regarding DTTSPROBE ,

if data is not available then how come it points to Next day,

lets say your QVD load missed on Monday then max(DTTSPROBE) date should be Sunday Correct?

else  How this date is getting populated in your raw data?

[stabben23]

Hi Amit,

I would say that you are not correct about how you load Your data. "but as we are generating data based on last reload time (Incremental load)" this is not true. You use Max timestamp in Your qvd.

Here you find max of DTTSPROBE which is the correct way of doing a incremental reload. You also save it into variable vLAST_LOAD_TIME.

MAX_TIME:

  load

     max(DTTSPROBE) as LAST_LOAD_TIME

  from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd) ;

  let vLAST_LOAD_TIME =  timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');

Later on you use this variable in Your SQL statement, here:

FROM $(vPlantAutor).STICHPROBE

WHERE DTTSPROBE > to_timestamp('$(vLAST_LOAD_TIME)', 'DD.MM.YYYY HH24:MI:SS');

I have 2 question here,

1 what is $(vPlantAutor). cant find the variable somewhere in script

2 why are you transform the date to 'DD.MM.YYYY hh:mm:ss' in Your Variable but use 'DD.MM.YYYY HH24:MI:SS' in SQL ? I should create the variable so you could use WHERE DTTSPROBE >= '$(vLAST_LOAD_TIME)'

So far I dont find any big problem, should work.

In last step, you should use you keys in a where not exists.

if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0  then

concatenate(STICHPROBE)

load

*

from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd)

Where not Exists(YourKey);

Even if you dont get any transactions on monday, it should find Max timestamp in Your qvd and from that timestamp reload New transactions.

[amit_saini]

Hi Staffan,

Sorry you are correct incremental load is based on max(DTTSPROBE).

We are missing some records on day basis , which might got updated either the same day or might be next days,so sorry I should mention that we never had a case of missing entire Monday's data ...we are just missing few records and to tackle with this situation we are looking for full load on Fridays , so that we can accommodate all missing records during weekly full load and not from scratch.

Answer to Your Questions:

• $(vPlantAutor) : We are using same script of 30 Shopfloor plants via include in form of .qvs file. So $(vPlantAutor) refer to different Plant names.
• why are you transform the date to 'DD.MM.YYYY hh:mm:ss' in Your Variable but use 'DD.MM.YYYY HH24:MI:SS' in SQL ? I should create the variable so you could use WHERE DTTSPROBE >= '$(vLAST_LOAD_TIME)'  : We are loading data from Oracle DB , it's more of timestamps entries and I think for matching the format we are doing so , but sorry I'm not 100% sure.

Thanks,

AS

[amit_saini]

we are having daily data , it just matter of missing few records. Please refer my reply to Staffan.

Thanks,

AS

[stabben23]

Ok Amit,

When you says that records are missing on monday but comes in later on. Will this means that the timestamp on these records is earlier than Your max(DTTSPROBE)? Then I see Your problem.

If you dont have any transactiondate which will be when the transaction get into the database then it could be a little bit tricky.

I should have test some inner join With the FROM $(vPlantAutor).STICHPROBE table. Then you have to know the keys in Your table. Something like this maybe:

concatenate(STICHPROBE)

load

Key, *

from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd)

Where not Exists(Key);

if weekday()=5 then //If Friday then check for missing records

inner join
load Key;
SQL SELECT Key FROM $(vPlantAutor).STICHPROBE;

end if