6 Replies Latest reply: May 22, 2017 9:11 AM by Peter Cammaert RSS

    Data Missing Issue Help !

    Amit Saini

      Hi Folks,

       

      I'm using below script for incremental load on QVD Generations:

       

      Let vStart = now();

      Let vReloadStart = now();

       

       

      STATION:

      LOAD

          '$(vPlant)' as Plant,

          *;

      SQL SELECT

          NLFDSTATIONNR,

          SSTATIONBEZ,

          SSTATIONNR

      FROM $(vPlantAutor).STATION;

       

       

      LET vSTATION_LOAD_DURATION =  Interval(now() - vStart) ;

      LET vStart = now();

       

       

      store STATION into $(vQVD)\$(vPlant)_STATION.qvd;

      store STATION into $(vQVDBackUp)\$(vPlant)_STATION.qvd;

       

       

      LET vSTATION_Size = num(filesize('$(vQVD)\$(vPlant)_STATION.qvd')/1024,'00.0') & ' KB';

      LET vSTATION_Records = QvdNoOfRecords ('$(vQVD)\$(vPlant)_STATION.qvd');

       

       

      drop table STATION;

       

       

      Now we are having very weird situation of missing data (As they are not got updated on time on DB) , so to get rid of this , is there any possibility to implement below logic.

       

       

      Day 1: incremental load + previous day

      Day 2: incremental load + 2 previous day

      Day 3: incremental load + 3 previous days

      Day 4: incremental load + 4 previous days

      Day 5: incremental load + 5 previous days

      Day 6: incremental load + 6 previous days

      Day 7: incremental load + 7 previous days

       

      If yes then please help me with above "STATION" table on script side.

       

      Thanks in advance!

       

      Regards,

      AS

        • Re: Data Missing Issue Help !
          Amit Saini

          Any suggestions???

           

          Thanks,
          AS

          • Re: Data Missing Issue Help !
            Peter Cammaert

            As far as I can see, your original script doesn't do an incremental load at all. It always does a full reload because of having no WHERE clause and as a result no restriction to juist the increment.

             

            What data are you lacking in the original script runs?

              • Re: Data Missing Issue Help !
                Amit Saini

                Hi Peter,

                 

                Sorry! Yes you are right.

                 

                Below is an example of incremental load on "STICHPROBE" table:

                 

                ************************************************************************************************

                 

                ///$tab STICHPROBE

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

                 

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

                 

                  MAX_TIME:

                  load

                     max(DTTSERF) as LAST_LOAD_TIME

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

                        //  where DTTSPROBE >= $(vRefTime)   // nicht notwendig

                  ;

                 

                  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,

                NSTPNR,

                NPERS_IDNR,

                NLFDSTATIONNR,

                NLFDMASCHNR,

                NLFDLINIENR,

                DTTSPROBE,

                DTTSERF,

                NSTPFEHLERFLAG,

                DXMIN,

                DTO,

                DTU,

                DXQ,

                SZUSINFO1,

                SZUSINFO2,

                SZUSINFO3,

                SZUSINFO4,

                SZUSINFO5,

                SZUSINFO6,

                SZUSINFO7,

                SZUSINFO8,

                SZUSINFO9

                FROM $(vPlantAutor).STICHPROBE

                WHERE DTTSERF > 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;

                 

                ***********************************************

                Actually we have some problem in updating DB , so some data instead of today will be getting updated on Thus (Let's Say) and doing incremental load alone will not work to show full data.

                 

                Hope you understood!

                 

                Thanks,

                AS

                  • Re: Data Missing Issue Help !
                    Peter Cammaert

                    Off the top of my head, here are two suggestions:

                     

                    • You can use the QvdCreateTime() function the define the cutoff point for your increment. Use Floor(QvdCreateTime()) to create a WHERE clause for the increment, like in

                      IF Not IsNull(QvdCreateTime(YourQVDFilePath)) THEN
                        LET vWHERE = "WHERE DTTSERF >= '" & date(Floor(QvdCreateTime(YourQvdPath)), 'DD.MM.YYYY') & "'" ;
                      ELSE
                        LET vWHERE = '';
                      END IF

                    • The second suggestion is about the cutoff date itself. IMHO it's more safe to ignore the last date in the historical QVD as there may be other rows added to your DB table on the day that you extract your increment. These wil be liost if you only load rows that are younger than the last day in the QVD. Or translated to your situation, add rows from the previous QVD file that are older than the cutoff date, not older or equal to that date. So add a WHERE clause to your CONCATENATE LOAD that is identical to the one specified earlier, except that the relational opperater is < (Less than) instead of >= (greater or equal than). You can use something like this:

                      IF (len(trim(vWHERE)) > 0) THEN // Historical QVD exists
                        CONCATENATE (STICHPROBE) LOAD *
                        FROM [YourQvdPath] (qvd)
                        WHERE DTTSERF < date(Floor(QvdCreateTime(YourQvdPath)), 'DD.MM.YYYY');
                      END IF

                     

                    Best,

                     

                    Peter