topic Re: Incremental load for salesforce in QlikView

Incremental load for salesforce

nirav_bhimani — Tue, 15 Apr 2014 13:36:45 GMT

Qlikview experts ,

I have tried following incremental script but when m taking data from data source and checking with last modified date getting script error as field 'LastModifiedDate' must be of type dateTime. So what kind of function should i use

//------------------------------------------script---------------------------------------------------------------//

if isnull(QvdCreateTime('..\QVDS\$(vQVDName).qvd')) then

$(vQVDName):

Select * from $(vTableName);

Store $(vQVDName) into ../Qvds/$(vQVDName).qvd;

Drop table $(vQVDName);

ELSE

TEMP:

LOAD

Timestamp(MAX(LastModifiedDate),'MM/DD/YYYY hh:mm:ss TT') as LAST_UPDATE_DATE

FROM..\Qvds\$(vQVDName).qvd(qvd);

Let vLastUpdateDate = Timestamp(peek('LAST_UPDATE_DATE',0,'TEMP'),'MM/DD/YYYY hh:mm:ss TT');

Drop table TEMP;

$(vQVDName):

// here m getting script error

Sql Select * from $(vTableName) where LastModifiedDate >= '$(vLastUpdateDate)';

if NoOfRows('$(vQVDName)') > 0 then

Concatenate

LOAD *

FROM

..\Qvds\$(vQVDName).qvd

(qvd)where not Exists(Id);

Inner join

Select Id from $(vTableName);

Store $(vQVDName) into ../QVDS/$(vQVDName).qvd;

Drop table $(vQVDName);

ENDIF

//--------------------------------script end-------------------------------------------//

script error

Registered_Company_Name__c from Account where LastModifiedDate >= '04/07/2014

ERROR at Row:1:Column:1782

value of filter criterion for field 'LastModifiedDate' must be of type dateTime and should not be enclosed in quotes

Account:

Sql Select * from Account where LastModifiedDate >= '04/07/2014 2:24:38 PM'

please help me to resolve this issue

thanks in advanced

regards

Nirav Bhimani

Re: Incremental load for salesforce

nirav_bhimani — Wed, 16 Apr 2014 05:03:31 GMT

Hi All,

Can you please look into above script for any solution.

Regards,

Nirav Bhimani

Re: Incremental load for salesforce

ariel_klien — Wed, 16 Apr 2014 05:45:32 GMT

Hi Nirav,

Did you try Sql Select * from Account where to_date(LastModifiedDate,'MM/DD/YYYY hh:mm:ss') >= $(vLastUpdateDate)

if it doesn't work, please send me the data that stored in the field LastModifiedDate.

Ariel

Re: Incremental load for salesforce

nirav_bhimani — Wed, 16 Apr 2014 05:50:13 GMT

Hi Ariel,

Thanks for your quick response, I have tried with to_date function also but its not working.

Regards,

Nirav Bhimani

Re: Incremental load for salesforce

ariel_klien — Wed, 16 Apr 2014 06:13:57 GMT

can you send me the data of this field

select LastModifiedDate

from table....

Re: Incremental load for salesforce

nirav_bhimani — Wed, 16 Apr 2014 10:01:01 GMT

this is the data of LastModifiedDate

Re: Incremental load for salesforce

Tue, 16 Dec 2014 12:29:20 GMT

I know this thread is rather old, but I had the same problems as Nirav. I managed to fix it this end by altering the format the vLastUpdateDate is in to the below (Refering to the original example code - my solution uses a different method of incremental load but had the same error as above around the dates):

TEMP:

LOAD

Timestamp(MAX(LastModifiedDate),'MM/DD/YYYY hh:mm:ss TT') as LAST_UPDATE_DATE

FROM..\Qvds\$(vQVDName).qvd(qvd);

Let vLastUpdateDate = Timestamp(peek('LAST_UPDATE_DATE',0,'TEMP'),'YYYY-MM-DD hh:mm:ss');

Drop table TEMP;

$(vQVDName):

Hope this helps someone who stumbles across this!

Cheers,

Phil.

Re: Incremental load for salesforce

mikkeltaylor — Tue, 16 Dec 2014 12:44:38 GMT

Thanks Phil. I'm glad I stumbled upon this!

I tried to load in SF data recently and this kept cropping up. I was only looking to do a PoC at the time, so I just converted it to perform a full reload each time.

I expect we'll take the PoC further so I'll definitely reuse the code you've provide

Regards, Michael