Incremental Load - Date Format

[bobbyn4u]

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>

[Anil_Babu_Samineni]

Is that LastModifiedDate has same format of your variable??

[stabben23]

Hi,

try With single quotes around the variable

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

[bobbyn4u]

Hi Anil,

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

[Kushal_Chawda]

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

[bobbyn4u]

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

[stabben23]

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

[balabhaskarqlik]

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

[Kushal_Chawda]

have you tried my solution?

[d_prashanthredd]

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.