Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
amit_saini
Honored Contributor III

Incremental load issue

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:MISmiley FrustratedS')

;

// 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

4 Replies

Re: Incremental load issue

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..

Life is so rich, and we need to respect to the life !!!
amit_saini
Honored Contributor III

Re: Incremental load issue

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

Re: Incremental load issue

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

Life is so rich, and we need to respect to the life !!!

Re: Incremental load issue

Is that question resolved? If so, Try to flag

Life is so rich, and we need to respect to the life !!!
Community Browser