topic Re: problem with incremental load in QlikView

problem with incremental load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I create an incremental load from sales force db.

the problem is that the qvd created in the incremental load is larger

then the qvd that created when i'm loading the whole table.

this is the incremental load:

LastModifiedDate:

LOAD MAX(LastModifiedDate) AS LastModifiedDate

FROM

[SF\sf increment\Contact.qvd](qvd);

// this is the only way to insert 'T' into the string- using replace.

//this is the format sales force demand: '2017-12-01T10:00:00-08:00'

let V_LastModifiedDate= timestamp(peek('LastModifiedDate',0,'LastModifiedDate'), 'YYYY-MM-DDuhh:mm:ss-00:00');

let @Contact_LastModifiedDate = Replace(@Contact_LastModifiedDate1,'u','T');

drop table LastModifiedDate;

// create the increment qvd

INCREMENT:

LOAD *;

SQL SELECT *

FROM Contact

WHERE LastModifiedDate > $(V_LastModifiedDate) ;

store INCREMENT INTO [SF\sf increment\INCREMENT.qvd](qvd);

DROP TABLE INCREMENT;

//merge the increment with the qvd

Contact:

LOAD *

FROM [SF\sf increment\INCREMENT.qvd](qvd);

Concatenate

LOAD *

FROM [SF\sf increment\Contact.qvd](qvd)

WHERE NOT EXISTS (Id);

store Contact into [SF\sf increment\Contact.qvd](qvd);

drop table Contact;

this is the load without increment:

Contact:

LOAD *;

SQL SELECT *

FROM Contact;

STORE Contact INTO [.\SF\Contact.qvd] (qvd);

Does anyone have an idea why

[SF\sf increment\Contact.qvd](qvd);

is not equal to

[.\SF\Contact.qvd] (qvd);

Thanks

omer

Re: problem with incremental load

shiveshsingh — Tue, 23 Jan 2018 07:06:33 GMT

What data is present in this QVD i.e. till when? Contact.qvd

Re: problem with incremental load

jonathandienst — Tue, 23 Jan 2018 07:17:40 GMT

I am a little confused here:

let V_LastModifiedDate= timestamp(peek('LastModifiedDate',0,'LastModifiedDate'), 'YYYY-MM-DDuhh:mm:ss-00:00');

let @Contact_LastModifiedDate = Replace(@Contact_LastModifiedDate1,'u','T');

What is @Contact_LastModifiedDate? I don't see it being used anywhere. Shouldn't the second line above apply to V_LastModifiedDate?

Re: problem with incremental load

Anonymous — Tue, 23 Jan 2018 08:37:46 GMT

Hi Shivesh,

Contact.qvd contain historical data until today.

Re: problem with incremental load

Anonymous — Tue, 23 Jan 2018 08:42:54 GMT

Hi Jonathan,

my mistake,

replace the lines:

let V_LastModifiedDate= timestamp(peek('LastModifiedDate',0,'LastModifiedDate'), 'YYYY-MM-DDuhh:mm:ss-00:00');

let @Contact_LastModifiedDate = Replace(@Contact_LastModifiedDate1,'u','T');

with

let V_LastModifiedDate1= timestamp(peek('LastModifiedDate',0,'LastModifiedDate'), 'YYYY-MM-DDuhh:mm:ss-00:00');

let V_LastModifiedDate = Replace(V_LastModifiedDate1,'u','T');

Re: problem with incremental load

sasikanth — Tue, 23 Jan 2018 10:30:06 GMT

Hi Try below

LastModifiedDate:

LOAD MAX(LastModifiedDate) AS LastModifiedDate

FROM

[SF\sf increment\Contact.qvd](qvd);

// this is the only way to insert 'T' into the string- using replace.

//this is the format sales force demand: '2017-12-01T10:00:00-08:00'

let V_LastModifiedDate= timestamp(peek('LastModifiedDate',0,'LastModifiedDate'), 'YYYY-MM-DDuhh:mm:ss-00:00');

let Latest_LastModifiedDate = Replace(V_LastModifiedDate,'u','T');

drop table LastModifiedDate;

// Latest Data

INCREMENT:

LOAD *;

SQL SELECT *

FROM Contact

WHERE LastModifiedDate > $(Latest_LastModifiedDate ) ;

// Merge Latest Data with existing data

Concatenate

LOAD *

FROM [SF\sf increment\Contact.qvd](qvd) WHERE NOT EXISTS (Id);

store INCREMENT into [SF\sf increment\Contact.qvd](qvd);

drop table INCREMENT ;

Re: problem with incremental load

jonathandienst — Wed, 24 Jan 2018 05:44:24 GMT

OK, you will need to wrap the date in quotes

INCREMENT:

LOAD *;

SQL SELECT *

FROM Contact

WHERE LastModifiedDate > '$(V_LastModifiedDate)' ;

Re: problem with incremental load

Anonymous — Wed, 24 Jan 2018 10:32:05 GMT

Sales force don't recognize the quotes ''

Re: problem with incremental load

Anonymous — Wed, 24 Jan 2018 10:33:33 GMT

Hi Sasi,

This is exactly what i'm doing.

The problem still happen