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
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?
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!!
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 ';
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.