15 Replies Latest reply: Sep 3, 2014 4:38 PM by Surender A RSS

    Qlikview MarketO Connector

      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

        • Re: Qlikview MarketO Connector
          John McTurnan

          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

            • Re: Qlikview MarketO Connector

              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

                • Re: Qlikview MarketO Connector

                  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

                    • Re: Qlikview MarketO Connector
                      John McTurnan

                      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

                        • Re: Qlikview MarketO Connector

                          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

                           

                           


                        • Re: Qlikview MarketO Connector
                          Shawn Merkle

                          Perhaps the double where is causing the syntax error?

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

                            • Re: Qlikview MarketO Connector

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


                                • Re: Qlikview MarketO Connector
                                  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'


                                    • Re: Qlikview MarketO Connector

                                      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

                                • Re: Qlikview MarketO Connector

                                  Hi John,

                                   

                                  I am trying to connect to Marketo and get data, can you please help me out and can you please email me the Qlikview Marketo connector exe. my email address is surender7@yahoo.com

                                   

                                  Thank you so much,

                                  Surender