10 Replies Latest reply: Feb 16, 2017 4:24 AM by Amit Saini RSS

    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

        • Re: Script help for missing records on Weekly basis
          Amit Saini

          any suggestion???

           

          Thanks,

          AS

          • Re: Script help for missing records on Weekly basis
            sasi k

            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?

            • Re: Script help for missing records on Weekly basis
              Staffan Johansson

              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.

                • Re: Script help for missing records on Weekly basis
                  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

                    • Re: Script help for missing records on Weekly basis
                      Staffan Johansson

                      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