Qlikview MarketO Connector

[#1]

Hi,

I'm in the process of building a connector which can get the lead data from MarketO. While I could build the connector successfully and get the data, I've an issue while trying to query the connector with a condition from the connector from Qlikview editor. My aim is to filter out records based on a time window and I get a QVX syntax error

QVX_SYNTAX_ERROR:

Invalid query: SELECT * FROM ActivityDetails where ActivityDateTime >

'10/12/2013 07:00:00 AM' AND ActivityDateTime < '10/12/2013 07:30:00 AM';

I get the data correctly if I don't use the where condition in the query (SELECT * FROM ActivityDetails;)

Can some body please tell me how can implement this?

Thanks,

John

[Anonymous]

Hi, it may be that your time format is not correct.  I built a vbscript macro in QV to query the Marketo SOAP API.  In that script I needed to format the time like this:  2013-04-10T11:43:03-06:00  otherwise the Marketo API would not accept it.

Let me know if you need anymore help building the QVX.  It is definitely something worth doing!!

Regards,

John McTurnan

[#3]

Hi John,

Thanks for your suggestions.

My issue is that, if I hardcode the date in the connector in the format I mentioned in my mail, it works fine however if I need to pass it from the QlikView editor it fails. 

I'll try change the format as you suggested from the editor and let you know if that resolves the issue.

Regards,

John George

[#4]

Hi John.

I tested with the modified query and still getting the same error. Following is the statements that I used in the qlikview editor

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

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

CUSTOM CONNECT TO "Provider=MarketOActivityConnector.exe;XUserId=JPFTXbD;XPassword=RVGDBOB;";
SQL SELECT *
FROM ActivityDetails
WHERE where ActivityDateTime > '2013-12-10T11:43:00-00:00'
AND ActivityDateTime < '2013-12-10T11:45:00-00:00 ';

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

Folowing is the error message

QVX_SYNTAX_ERROR: Invalid query: SELECT *

FROM ActivityDetails

WHERE where ActivityDateTime > '2013-12-10T11:43:00-00:00'

AND ActivityDateTime < '2013-12-10T11:45:00-00:00 '

SQL SELECT *

FROM ActivityDetails

WHERE where ActivityDateTime > '2013-12-10T11:43:00-00:00'

AND ActivityDateTime < '2013-12-10T11:45:00-00:00 '

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

Let me know in any way you can help me on this?

Thanks,

John

[Anonymous]

Try this... Salesforce likes its dates without single quotes around them.  Like this example:

SQL SELECT *

FROM Lead

WHERE

   ConvertedOpportunityId != NULL

  AND

  CreatedDate >= 2012-04-01T00:00:00.000+00:00

Regards,

John

[shawn_merkle]

Perhaps the double where is causing the syntax error?

WHERE where ActivityDateTime > '2013-12-10T11:43:00-00:00'

[#7]

No John. I still have the issue and what I'm not sure is that whether I'll be able to filter out the records by using a query from the QlikView editor..

When I compare the MarketO connector I built with the salesforce one, I think the way I get the data and the way salesforce connector gets the data is different. It is my assumption that behind the screen salesforce connector issues some kind of SQL query to the underlying database to get the results so when you issue the query "SELECT * FROM Lead WHERE CreatedDate >= 2012-04-01T00:00:00.000+00:00" the QlikView editor pass this query to the connector and get the data.

While in the case of MarketO, there is no way to send a direct query to their underlying databases to get the results. So I'm using the web services for that and I'm using getLeadChanges method. This method accepts many parameters and I'm trying to use the latestCreatedAt and oldestCreatedAt date parameters. If I hard code these parameters in the connector it works fine. But my intension is to pass this parameters from the QLikView editor and has no success in it.

Hope I'm explaining it clearly.  I will really appreciate you throwing any ideas or suggestions that help me to resolve this.

Regards,

John

[#8]

Thanks Shawn for your suggestions. I tried it without the quotes and still gives me the same error.

[shawn_merkle]

John V not the double quotes but the fact that Where is in the line of code twice.

WHERE where ActivityDateTime > '2013-12-10T11:43:00-00:00'

[#10]

Thanks Shawn for noticing it. However I still get the same result when I ran the corrected query

QVX_SYNTAX_ERROR: Invalid query: SELECT *
FROM ActivityDetails
WHERE ActivityDateTime > '2013-12-10T11:43:00-00:00'

SQL SELECT *
FROM ActivityDetails
WHERE ActivityDateTime > '2013-12-10T11:43:00-00:00'
-------------------------------------------------------------------------
QVX_SYNTAX_ERROR: Invalid query: SELECT *
FROM ActivityDetails
WHERE ActivityDateTime > 2013-12-10T11:43:00-00:00

SQL SELECT *
FROM ActivityDetails
WHERE ActivityDateTime > 2013-12-10T11:43:00-00:00

John