Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
What data is present in this QVD i.e. till when? Contact.qvd
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?
Hi Shivesh,
Contact.qvd contain historical data until today.
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');
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 ;
OK, you will need to wrap the date in quotes
INCREMENT:
LOAD *;
SQL SELECT *
FROM Contact
WHERE LastModifiedDate > '$(V_LastModifiedDate)' ;
Sales force don't recognize the quotes ''
Hi Sasi,
This is exactly what i'm doing.
The problem still happen