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

[amit_saini]

Any suggestions???

Thanks,
AS

[kamal_sanguri]

Concatenate your incremental load with additional load for additional days (yesterday to past seven days).

So, something like

Let vday =1;

Incremental Load

Concatenate

If vday=1 then

Load data

where date = today()-1;

Let vday = $(vday)+1;

elseif vday=2 then

Load data

where date > today()-2;

Let vday = $(vday)+1;

.

.

.

.

elseif vday=7 then

Load data

where date > today()-8;

Let vday = 1;

Endif

[amit_saini]

Hi Kamal,

Thanks!

I will try this and will let u know in cases it's working.

Regards,

Amit

[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?

[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

[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