topic Re: Not able to edit Salesforce tables in Data Editor in Integration, Extension & APIs

Not able to edit Salesforce tables in Data Editor

hodgesjr0 — Fri, 17 Nov 2023 15:34:39 GMT

Hello. I am attempting to load a Salesforce table and have a WHERE clause, but I mostly get the following error. I've removed the WHERE clause and the table loads correctly.

1. Why can I not add a WHERE clause into the [Campaign] table?

2. How do I get this to load with the WHERE clause?

3. Is the WHERE clause the actual issue? (date format incompatibility?)

//////////////////////////////

The following error occurred:

Batch was not processed, status: Failed : InvalidBatch : Failed to process query: MALFORMED_QUERY: unexpected token: A12

///////////////////////////////

Values:

LOAD

Variable,

Value

FROM [lib://Data Inbox - Qlik/Qlik-Scripts/Warehouse ETL/Warehouse ETL - Salesforce/LastReload.txt]

(txt, utf8, embedded labels, delimiter is spaces, msq);

LET vPullDateTimeStamp = Timestamp(Peek('Variable',0,'Values'),'YYYY-MM-DDThh:mm:ssZ');

LET vPullDateDateOnly = date(Peek('Variable',0,'Values'));

LET vNumPull = Num(Date(peek('Variable',0,'Values'), 'MM/DD/YYYY'));

LIB CONNECT TO 'Data Source - Salesforce';

[Campaign]:

SELECT

LastModifiedDate

FROM Campaign

WHERE DATE(LastModifiedDate) >= $(vPullDateTimeStamp)

;

DISCONNECT;

Re: Not able to edit Salesforce tables in Data Editor

WaltShpuntoff — Sat, 18 Nov 2023 12:11:02 GMT

You need to match the variable / query to match a regular SF query.

Qlik just passes things through to SF. The error message should show you what the offending line was. It is most likely that $(vPullDateTimeStamp) is either not in the correct/expected format, or it just needs single quotes around it to make it a string.

WHERE DATE(LastModifiedDate) >= '$(vPullDateTimeStamp)'

hth

-ws

Re: Not able to edit Salesforce tables in Data Editor

sidhiq91 — Sat, 18 Nov 2023 12:29:48 GMT

@hodgesjr0 From the above script I see that LastModifiedDate is in Date format and vPullDateTimeStamp is in Timestamp format. Please keep them both in the same format and then try to execute the query.

Re: Not able to edit Salesforce tables in Data Editor

hodgesjr0 — Mon, 27 Nov 2023 14:12:22 GMT

Thank you for your speedy reply.

Unfortunately, the format of [LastModifiedDate] is an odd version of a datetime stamp.