topic Incremental Load - Date Format in QlikView

Incremental Load - Date Format

bobbyn4u — Thu, 29 Jun 2017 09:07:29 GMT

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

---------------

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>

Re: Incremental Load - Date Format

Anil_Babu_Samineni — Thu, 29 Jun 2017 09:09:32 GMT

Is that LastModifiedDate has same format of your variable??

Re: Incremental Load - Date Format

stabben23 — Thu, 29 Jun 2017 09:43:25 GMT

Hi,

try With single quotes around the variable

FROM Account where LastModifiedDate > '$(vLastModifiedDate)';

Re: Incremental Load - Date Format

bobbyn4u — Thu, 29 Jun 2017 09:47:32 GMT

Hi Anil,

We used the same format and got the same error as mentioned above.

Re: Incremental Load - Date Format

Kushal_Chawda — Thu, 29 Jun 2017 09:52:11 GMT

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');

Re: Incremental Load - Date Format

bobbyn4u — Thu, 29 Jun 2017 09:55:24 GMT

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

Re: Incremental Load - Date Format

stabben23 — Thu, 29 Jun 2017 10:18:44 GMT

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) & ')';

Re: Incremental Load - Date Format

BalaBhaskar_Qlik — Thu, 29 Jun 2017 10:22:49 GMT

LastModifiedDate field value & vLastModifiedDate Variable value Timestamp are not similar, think so.

Re: Incremental Load - Date Format

Kushal_Chawda — Thu, 29 Jun 2017 10:25:40 GMT

have you tried my solution?

Re: Incremental Load - Date Format

d_prashanthredd — Thu, 29 Jun 2017 10:45:38 GMT

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.

Re: Incremental Load - Date Format

bobbyn4u — Thu, 29 Jun 2017 14:15:08 GMT

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?

Re: Incremental Load - Date Format

bobbyn4u — Fri, 30 Jun 2017 09:45:40 GMT

Not working Kushal, getting the error.

Re: Incremental Load - Date Format

Kushal_Chawda — Fri, 30 Jun 2017 09:50:11 GMT

what error?

Re: Incremental Load - Date Format

bobbyn4u — Fri, 30 Jun 2017 20:51:17 GMT

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 ','

Re: Incremental Load - Date Format

Anonymous — Wed, 05 Jul 2017 19:22:29 GMT

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.