Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

This new version replaces v1 of this connector. Please note that the docs and functionality are still the same as for v1 with the exception being that this new version has the new UI style.

This new version also has additional Insert and Update tables which means you can actually write data back to Salesforce from QlikView or Qlik Sense (i.e. as the load script runs).


Usage

Note that most of the functionality is similar to v1 of this connector with the exception of new Insert and Update tables which have been added.

Authentication

You should first authenticate by clicking this button:

Image

You will be presented with a Salesforce login screen where you can enter your details and log in

Image

In some cases, for security reasons, Salesforce may require you to verify who you are. Simply click the 'Email me a verification code' button to get an email.

Image

After a few moments you'll receive an email from Salesforce with a verification code, which will expire after 24hrs. If you don't receive this code, please check your spam folder.

Image

Meanwhile, QVSource will now be presenting you with a screen to enter the verification code. Enter it and click the verify button.

Image

The final step is to give QVSource some permissions:

Image

Once these steps are complete you will see an authentication token in QVSource. You can run the 'CanAuthenticate' table to check authentication is working correctly.

Image

Tables

A simple way to check that you can get data back at this stage is to select and run the 'id' table, where you should then get information about the authenticated account.

Image

Getting Data

QVSource gives you three ways to get data; using Queries, Search or building a General Request - this is a more advanced way of getting data, but will allow you to pretty much get any table you want.

We'll cover each of these methods below.

Before attempting to get data, please check you are using the relevant API number. The default is v33.0, all available versions can be found using the APIVersions table.

Image

Salesforce Workbench

For both SOQL Query and SOSL Search tables, you can use the Salesforce Workbench to help you construct the query/search strings.

You will then be taken to the Workbench on Developerforce.com. Select the relevant Environment and API version - in most cases it'll be the Production environment.

Please note that the API version available here may include a more recent API number than that displayed in QVSource. This newer version is the next in development at Salesforce, so please ensure that you have the same API version selected here as in QVSource.

Image

Enter your Salesforce credentials:

Image

You will then be asked to give the Workbench permission

Image

You can now choose to either build a SOQL Query or SOSL Search string.

Query - for SOQLQuery Tables

If you want to work with SOQL Queries, then select SOQL Query from the list:

Image

You can then pick an object. In this example, we'll work with Opportunities

Image

You will then be taken to a page where the query string can be built up. As you pick fields, the SOQL query is made for you and on pressing 'Query' it'll be executed:

Image

The results from the query are shown below the builder:

Image

Since this is essentially a SQL type syntax, we can edit the query directly. So, here we've typed the ID field into the query box.

Image

And now our results show 2 columns of data.

Image

Once you're happy with the query string; select and copy it

Image

then paste it into QVSource

Image

On running the table, the query will be executed and pulled into QVSource.

Image

As with all other QVSource connectors you can then copy the script and put it into your QlikView / Qlik Sense applications.

Image

One important note, if you make a query and one of the columns contains no data for any of the rows in the result set then this column will be excluded from the resultant table as well. So for example if you use the query:
select Id, Username, UserRoleId from User

But the UserRoleId is empty for all users then then resultant table will only contain the Id and Username columns. This appears to be a feature of the API.

Search - for SOSLSearch Tables

From the Workbench home page, select SOSL Search from the list.

Image

You don't need to pick an object and can just click 'Select' where you will be presented with the Search string builder:

Image

In this example we want to return Opportunity ID's where Name fields contain the word 'Edge'.

So, we fill in the 'Search For' box and pick 'Opportunity' for the returning object. As we do this, the search string is constructed:

Image

Clicking on 'Search' returns the relevant records.

Image

Once we're happy with the Search string we can use it in our QlikView applications. So, copy the string:

Image

and paste it into QVSource

Image

On running the table, the search string is executed

Image

You can then go to the QlikView / Qlik Sense Load Script tab, to retrieve the script to put into your applications.

Image

Using the Search and Query Tables Together

In the example above we had the following result from the search term 'Edge'

Image

Going back to the SOQL Query builder we can look for the first search result record like so:

Image

With this in mind, in your QlikView application, it would be quite easy to edit the SOSLSearch string in the QlikView script and replace the search term 'Edge' with a variable so that you could loop through a number of search terms retrieving and accumulating their results.

Similarly the SOQLQuery string could have it's ID parameter replaced with a variable.

By looping through the SOSLSearch result ID's and assigning them to the SOQL Query variable you could easily build up the opportunity data based on a series of search terms.

Using the Salesforce API (Advanced Users)

If the SOQLQuery and SOSLSearch options aren't giving you the information that you need, you can also use the Salesforce API to construct your own query string, which will return either the Raw XML or you can pick out particular tables from the XML.

We're going to be using the Salesforce API to help construct a query string which will return all the news for the signed in user and we'll also show how you can search feed elements for a particular term.

Note that Feed Item resources aren’t available in version 32.0 and later. In version 32.0 and later, use Feed Elements resources.

Firstly, go to the Salesforce API documentation site and navigate to the Chatter API resources.

Image

The highlighted area shows the different types of information that you can get.

All Records Being Followed Example

In this first example we'll be getting all the news for the signed in user.

From the menu click on 'News Feed Resources' and on the right you can see that the 3rd resource is the one we're interested in

Image

If we click on the resource we'll be shown a few more details about it

Image

Copy the url that is highlighted and then in QVSource select the 'RequestRawXml' table and paste the URL - make sure you remove the leading '/' so that the url starts with 'chatter'. You also need to update 'userId' to 'me'.

Image

Now run the table. QVSource will pass the query url over to Salesforce and show the xml returned.

Image

If this xml is all you need, you can copy the url at the top and paste it into your QlikView application. You can do this by going to the Load Script editor in QlikView and selecting the 'Web Files' button. You can then paste the url into the 'Internet File' box and follow the wizard steps:

Image

It is also possible to pick out each of the feedElements and bring those into your QlikView application.

To do this, select the 'Request' table and in the XPATH box, enter '//feedElement' - please ensure that you get the capitalisation correct.

Image

Now run the table. The QlikView load script generated by QVSource can now be copied and pasted into your application.

Image

Searching for a Feed Element

In this next example we're going to search for feed elements that contains the word 'Yammer'.

So, go to the Salesforce API documentation and find the 'Feed-Elements Resources' page

We are going to be using the first resource.

Image

Clicking on the resource takes you to some more details about it:

Image

In QVSource select the 'RequestRawXml' table enter chatter/feed-elements as the URL. We also need to enter q=yammer as the Parameters and //elements as the XPATH.

Image

Now run the table.

Image

If this xml is all you need, you can copy the url at the top and paste it into your QlikView application. You can do this by going to the Load Script editor in QlikView and selecting the 'Web Files' button. You can then paste the url into the 'Internet File' box and follow the wizard steps:

Image

However, if you want a table of the individual feedElements, you can use the 'Request' table and XPATH box as above.

Insert

We can view existing accounts using the following query: SELECT ID, Name FROM Account

Image

Now select the 'Insert' table and enter sobjects/Account in the URL We also need to enter JSON data that represents a new Account: { "Name" : "Autorent plc", "Type" : "New account opportunity" }

Image

Run the table, and if successful we see the following result:

Image

Re-querying we can see the new account has been added:

Image

Update

We now realise a mistake has been made; we need to rename 'Autorent plc' to 'Autorent INC'. Note the Id field in this case is '00124000008gGbYAAU', copy your Id field for use below.

We want to update a single account, so we will use the id in the update URL. Select the 'Update' table and enter sobjects/Account/00124000008gGbYAAU in the URL, replacing '00124000008gGbYAAU' with your Id. The update JSON will be: { "Name" : "Autorent INC" }

Image

Run the table, and if successful we see the following result:

Image

Re-querying we can see the account has been updated:

Image

Limitations

The connector does not currently support Salesforce sandboxes. Please contact us if you need this feature.

Change Log

0.9.2 - 23/03/16
  • RequestRawXml no longer errors when application/json is returned from the API request but converts this to XML.

0.9.1 - 14/04/15
  • Updated default API version to use to v36.0.

0.9.0 - 14/04/15
  • Initial release of V2.
  • Includes new Update and Insert tables.
  Name Size
- apiversions.png 84.53 KB
- auth.png 75.97 KB
- auth2.png 80.26 KB
- chatter_search_query.png 82.40 KB
- chatter_search_results.png 154.27 KB
- chatter_search_webpage.png 157.15 KB
- chatter_search_webpage_2a.png 143.34 KB
- chatter_webpage.png 154.36 KB
- chatter_webpage_2a.png 151.91 KB
- chatter_webpage_3a.png 150.48 KB
- feedElements.png 95.84 KB
- feedElements_script.png 119.19 KB
- general.png 95.94 KB
- genera_res.png 173.67 KB
- id.png 96.23 KB
- insert.png 97.36 KB
- insert_after.png 103.48 KB
- insert_before.png 106.26 KB
- insert_res.png 97.20 KB
- salesforce v2.png 112.48 KB
- soql.png 82.77 KB
- soql_res.png 109.84 KB
- soql_script.png 108.47 KB
- sosl.png 109.97 KB
- sosl_res.png 78.20 KB
- sosl_script.png 98.57 KB
- update_before.png 117.74 KB
- update_requery.png 97.33 KB
- update_res.png 81.91 KB
- yam.png 80.84 KB
- yam_res.png 150.61 KB


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd