We are working on Incremental Load for Salesforce data. The script is working fine in Qlikview but the same is not working in Qlik Sense.
We tried in different date formats but the result is same. Please help in this.
Last_Updated_Date:
load Timestamp(MAX(LastModifiedDate),'MM/DD/YYYY hh:mm:ss TT') as MaxDate From C:\QlickView\Analysis\SFDC_Accounts.qvd (qvd);
Let vLastModifiedDate = Timestamp(peek('MaxDate',0,'Last_Updated_Date'),'YYYY-MM-DD hh:mm:ss');
DROP Table Last_Updated_Date;
Incremental:
LOAD
Id,
---------------
LastModifiedDate,
;
SQL SELECT
Id
---------------
LastModifiedDate
FROM Account where LastModifiedDate > $(vLastModifiedDate);
Concatenate
LOAD
Id,
---------------
LastModifiedDate,
FROM C:\QlickView\Analysis\SFDC_Accounts.qvd(qvd) where not Exists(Id);
Inner Join
LOAD Id; SQL select Id from Account;
STORE Incremental into C:\QlickView\Analysis\SFDC_Accounts.qvd(qvd);
DROP Table Incremental;
SFDCAccounts:
LOAD * from C:\QlickView\Analysis\SFDC_Accounts.qvd (qvd);
Error:
QVX_UNEXPECTED_END_OF_DATA: Batch was not processed, status: Failed : InvalidBatch : Failed to process query: MALFORMED_QUERY: LastModifiedDate > 2017-06-29 08:21:27 ^ ERROR at Row:23:Column:57 line 23:57 no viable alternative at character '<EOF>
The same script is working in the Qlikview, but it is not working in Qlik Sense. We have tried different formats for dates even though getting the same error which has mentioned in the above. Can anyone help me on this?
I have used the below logic and got the below error which has highlighted in colour.
where to_timestamp(to_char(LastModifiedDate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')> to_timestamp('$(vLastModifiedDate)','YYYY-MM-DD HH24:MI:SS');
Error:
QVX_UNEXPECTED_END_OF_DATA: Batch was not processed, status: Failed : InvalidBatch : Failed to process query: MALFORMED_QUERY: to_timestamp(to_char(LastModifiedDate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD ^ ERROR at Row:23:Column:56 expecting a right parentheses, found ','
I was having an issue with Qlikview getting a very similar error message. I stumbled on this thread attempting to solve my issue. After a little back and forth I was able to resolve the issue, however; you were asking about Qliksense and I'm not certain if my solution will help you.
The reason I received the error was due to our Salesforce Connector. We had recently updated our connector and there were a few optional parameters that needed to be used with the connector. In the Load Editor when Connecting using the QvSalesforceConnector.Exe, I used Bulk API Interface Type with Convert SQL Dates, Convert UTC to local time, and PK Chunking. I believe my issue was related date formats and the Convert SQL Dates or Convert UTC to local time option managed to resolve it. Hopefully this helps.
