topic Re: Incremental load issue in QlikView

Incremental load issue

amit_saini — Fri, 15 Dec 2017 05:53:19 GMT

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,

Message was edited by: Amit Saini

Re: Incremental load issue

Anil_Babu_Samineni — Fri, 15 Dec 2017 06:12:36 GMT

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

Re: Incremental load issue

amit_saini — Fri, 15 Dec 2017 06:16:55 GMT

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,

Re: Incremental load issue

Anil_Babu_Samineni — Fri, 15 Dec 2017 06:32:30 GMT

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

Re: Incremental load issue

Anil_Babu_Samineni — Wed, 20 Dec 2017 07:49:34 GMT

Is that question resolved? If so, Try to flag