Incremental load issue

[amit_saini]

Hi Folks,

I'm using below script for incremental load from SQL DB , but it's not working at all. Please suggest what's wrong:

if filesize('$(vQVD)\CAQ_ABC.qvd') >0  then

  trace >>>>>> $(vQVD)\CAQ_ABC.qvd    ;

MAX_TIME:

  load

     max(DTTSERF) as LAST_LOAD_TIME

  from $(vQVD)\CAQ_ABC.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

CAQ_ABC:

LOAD "Facility_Id",

    Date,

    Product,

    Variant,

    Station,

    Serial,

    Characteristic,

    "Values",

    Status,

    DTTSERF;

SQL SELECT *

FROM MasterCAQ.dbo."CAQ_ABC"

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

;

// load prior values

if filesize('$(vQVD)\CAQ_ABC.qvd') >0  then

concatenate(CAQ_ABC)

load

*

from $(vQVD)\CAQ_ABC.qvd (qvd);

end if

// store all values

store CAQ_ABC into $(vQVD)\CAQ_ABC.qvd;

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

LET vStart = now();

LET vCAQ_ABC_Size = num(filesize('$(vQVD)\CAQ_ABC.qvd')/1024,'00.0') & ' KB';

LET vCAQ_ABC_Records = QvdNoOfRecords ('$(vQVD)\CAQ_ABC.qvd');

drop table CAQ_ABC;

Regards,

AS

Message was edited by: Amit Saini

[Anil_Babu_Samineni]

Amit Saini wrote:

Hi Folks,

I'm using below script for incremental load from SQL DB , but it's not working at all. Please suggest what's wrong:

MAX_TIME:

  load

     max(DTTSERF) as LAST_LOAD_TIME

  from $(vQVD)\CAQ_ABC.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

Is there any IF statement for this? If this is the full script you have Else won't work. Could be reason..

[amit_saini]

Sorry Anil,

Forgot below lines

if filesize('$(vQVD)\CAQ_ABC.qvd') >0  then

  trace >>>>>> $(vQVD)\CAQ_ABC.qvd    ;

and than it starts as per above comments.

But in general is there any way to do incremental load based on last reload time for below example:

CAQ_ABC:

LOAD "Facility_Id",

    Date,

    Product,

    Variant,

    Station,

    Serial,

    Characteristic,

    "Values",

    Status,

    DTTSERF;

SQL SELECT *

FROM MasterCAQ.dbo."CAQ_ABC";

Thanks,

AS

[Anil_Babu_Samineni]

May be this?

CAQ_ABC:

LOAD "Facility_Id",

    Date,

    Product,

    Variant,

    Station,

    Serial,

    Characteristic,

    "Values",

    Status,

    DTTSERF;

SQL SELECT * FROM MasterCAQ.dbo."CAQ_ABC";

Store CAQ_ABC into [FilePath...];

Drop Table CAQ_ABC;

Sample:

Load * From <Your QVD>;

Last_Update_Reload:

Load Max(Date) as Max_Date Resident Sample;

Let VarForMax = Peek('Max_Date', 0, 'Last_Update_Reload');

Drop Table Sample;

In future, The Date may extend from current Max date to till next 2 Years. That means this?

First:

Load * From Sample_External_Source Where Date > $(VarForMax);

Concatenate

Load * From <Sample Qvd Path>;

Store First into ...;

Drop Table First;

Now, Load Fresh Table into QVD which is Last one

Load * From <First>;

[Anil_Babu_Samineni]

Is that question resolved? If so, Try to flag