Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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>
Is that LastModifiedDate has same format of your variable??
Hi,
try With single quotes around the variable
FROM Account where LastModifiedDate > '$(vLastModifiedDate)';
Hi Anil,
We used the same format and got the same error as mentioned above.
try this
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');
Hi Staffan,
We have tried with quotes also (FROM Account where LastModifiedDate > '$(vLastModifiedDate)';) , we got the same error.
The following error occurred:
QVX_UNEXPECTED_END_OF_DATA: Batch was not processed, status: Failed : InvalidBatch : Failed to process query: INVALID_FIELD: FROM Account where LastModifiedDate > '2017-06-29 08:21:27' ^ ERROR at Row:23:Column:20 value of filter criterion for field 'LastModifiedDate' must be of type dateTime and should not be enclosed in quotes
ok, try this then:
FROM Account WHERE LastModifiedDate >= to_timestamp(' & chr(39) & '$(vLastModifiedDate)' & chr(39) & ',' & chr(39) & 'YYYY-MM-DD HH24:MI:SS' & chr(39) & ')';
LastModifiedDate field value & vLastModifiedDate Variable value Timestamp are not similar, think so.
have you tried my solution?
I think same error even if we convert field and compare with a variable.
All conversions are working fine in Qlikview but nothing is working in Qlik Sense.