problem with incremental load

[Anonymous]

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

[shiveshsingh]

Hi

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

[jonathandienst]

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?

[Anonymous]

Hi Shivesh,

Contact.qvd contain historical data until today.

[Anonymous]

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

[sasikanth]

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 ;

[jonathandienst]

OK, you will need to wrap the date in quotes

INCREMENT:

LOAD *;

SQL SELECT *

FROM Contact

WHERE LastModifiedDate > '$(V_LastModifiedDate)' ;

[Anonymous]

Sales force don't recognize the quotes ''

[Anonymous]

Hi Sasi,

This is exactly what i'm doing.

The problem still happen