While using the incremental load script for salesforce data, in Qliksense i am facing the below error :
i am using the below code for the incremental load :
kindly assist .
Thanks in advance.
Does it work in QlikView ?
I think you may try using quotes in your where condition. Something like below.
WHERE LastModifiedDate >= '2015-07-26 10:10:00' and ....
I'm almost certain it is a WHERE clause issue. Can you test that by changing the where clause with different filters?
Can you check the format of LastModified field?
I would love to try myself, but not sure if you can send it or would it work even if you send.
Is it possible you try with QlikView? In order to connect to SalesForce from QlikView there is a special connector.
I think it has something to do with the way the Qlik Sense SalesForce connector handles the SOQL query and that differs from the way that QlikView SalesForce connector handles it.
The following where clause works:
WHERE LastModifiedDate >= 2015-09-17T17:19:35Z and LastModifiedDate < 2015-09-18T08:50:00Z;
But now I am struggling to get the appropriate timestamps into the variables.
I believe UTC timezone is used in the SF database, but I am not yet sure how to retrieve the data in local time.
Joost Romijn wrote:
Thank you for your response.
I tried this, but unfortunately this didn't work.
Not very informative...
Did the error message change?
Did you apply the quotes to both dates?
Is the date format you are using understood by the server?
Maybe you can convert the WHERE clause to that specific format SF is using.
Functions like subfield() may help. Did you try that?
You can also add subtract the your timestamp variables such as
This one for example adds 3 hours to the UTC timestamp, which is Istanbul timezone. So if we can modify everything then I believe you can run the SQL in the requested format. Don't you think?
I think I have found a working solution.
I needed to convert the QvdCreateTime to UTC time. I could not get that working with the ConvertToLocalTime function, neither could I use a fixed substract since we have summer & winter time.
I fixed it like this, though I believe there should be an easier way:
LET vLastExecTime = timestamp(if(isnull(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD')),0,QvdCreateTime('$(vQVDPath)ReloadHistory.QVD')-(NOW()-UTC())), 'YYYY-MM-DD hh:mm:ss');
LET vExecTime = timestamp(UTC(), 'YYYY-MM-DD hh:mm:ss');
LET vLastExecTimeSF = Date('$(vLastExecTime)','YYYY-MM-DD')&'T'&TIME('$(vLastExecTime)','hh:mm:ss')&'Z';
LET vExecTimeSF = Date('$(vExecTime)','YYYY-MM-DD')&'T'&TIME('$(vExecTime)','hh:mm:ss')&'Z';
vLastExecTimeSF then returns 2015-09-18T08:55:00Z. If I understood correctly from the SOQL documentation, this should return 2015-09-18 10:55:00, which is the Dutch local time.