Welcome Guest, you are in: Login

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

IMPORTANT: If you are upgrading to version 1.6.5.3 or later from a version earlier than 1.6.5.3 please ensure you read this page.


APIs

The sentiment analyser and text analytics connector allows you to add a sentiment score for text fields in your application. Additionally, some of the APIs we connect to (see list below) also provide other text analytics features such as POS (parts of speech), topic, concept and keyword extraction etc.

What's more, with the new asynchronous and batch processing functionality added into QVSource 1.4.0.4 and later, you can now process large volumes of text in a very efficient and performant manner, further reducing the time your applications take to reload.

Here are the results of a couple of test runs scoring 500 Tweets and showing the large performance improvements seen:

Image

Image

This connector currently connects to 5 different APIs (plus an internal regular expression parser):

  • AlchemyAPI (sign up for an API key here). Supports English and German. Please contact AlchemyAPI for pricing. (Also supports Concepts, Categories, Keywords (with or without sentiment) and Language extraction.) You can also find QVSource listed under the AlchemyAPI 3rd Party Tools.

  • MeaningCloud (you will need to sign up for a free or commercial API key on their website). NOTE - support for this was only added in QVSource 1.6.5.3 and currently only for the sentiment analysis. Please contact us if there are other parts of the API you would like us to access. Supports English, Spanish and French.

  • Repustate (sign up for an API key here). Supports English, Spanish, German, French, Italian, Chinese, Arabic, Russian and Polish (QVSource version 1.6.1.9 and later). You can find a test interface here and pricing see here. (Also supports 'Parts of Speech' (POS) extraction of verbs, nouns and adjectives, and extraction of the sentiment of individual parts of the document and individual categories in the document for different niches including hotel, airline, telco, retail and restaurants). Repustate also have a server product which can be installed at your own premises.

  • Saplo. Supports English and Swedish (but can be trained for other languages). Their sentiment feature is currently in beta - see this page for a full demo script and more information.

  • Sentiment140. Supports English and Spanish. We will also be covering the commercial fee of using this service. Note that the QVSource connector translates the sentiment scores from this API to -1 (negative), 0 (neutral) or +1 (positive). Note also that this API is only for classifying short 'Tweet length' strings only - long strings will result in an error and a sentiment classification of 0. This API is free to use. Supports English, Spanish coming in new version of Connector.

  • Regular Expression (RegEx) engine. QVSource version 1.5.2.7 and later contains a RegEx option in this connector which allows you to perform some regular expression parsing during your QlikView or Qlik Sense load script process. See an example here and please let us know what other features you would like to see.

Please see the documentation for the relevant API for further information on how the score is calculated. You should also register with the provider for an API key which you will then need to enter into QVSource.

If you have a suggestion for other APIs/algorithms we can include please let us know.

If you are interested in sentiment analysis and text analytics from QlikView and Qlik Sense, please also check out the:
If you are interested in being able to construct your own model to predict anything else (including sentiment), please also see the Google Predict Connector.

Additional Costs

Some sentiment engines have additional charges, which depend on your usage levels - some engines have 'free' or 'starter' levels, which are suitable for many users.

Any subscription that you take is directly with the sentiment company, not us - QVSource gives you the interface to enter your unique subscription token if you have one.

Please refer to the individual engines in the section above and visit their respective websites for more information.

Usage

In order to use this connector you should have already loaded a table into your QlikView application containing the text you wish to analyse. In the example below we have loaded a table named 'Timeline' with a text field named 'text' earlier in the load script.

We then use one of the following scripts to loop through each row in the 'Timeline' table and create an additional table with the associated sentiment for each message.

Scenario 1 - Use processParams???? Method (Recommended)

In QVSource 1.4.0.7, we introduced a new technique for making multiple QVSource requests without requiring a for/next loop and does not require any of the URL encoding explained below.

This mechanism of making multiple synchronous, asynchronous and batch calls offers huge performance advantages for this connector and is the recommended way of using the connector.

You can see various examples of using this Connector using this technique here.

Scenario 2 - Use QlikView For/Next Loop

The above scenario 1 is recommended, however if you prefer to code individual requests to this Connector in your load script you can use one of the techniques below.

Scenario 2a (Text Already URL Encoded)

In QVSource 1.2.5 we introduced url encoded versions of most of the text columns returned by the connectors, removing the need for the additional encoding text shown in the version below.

So if you have text in your application which is already url encoded you can use script similar to the following.


LET noRows = NoOfRows('Timeline');
   
for i=0 to $(noRows)-1
	
     let textEncoded = peek('text_urlEncoded', $(i), 'Timeline');
     let text = peek('text', $(i), 'Timeline');
	
     Sentiment:
     LOAD
    	'$(text)' as text,
    	status as sentiment_status,
    	score as sentiment_score
     FROM
     [http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Repustate&format=qvx&Message=$(textEncoded)]
     (qvx);
next

Note - In the above script the table name Sentiment_Repustate contains the sentiment API being used.

If we wish to use one of the other sentiment APIs we can simply change this, for example:

http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_AlchemyAPI&Message=$(text)
http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Sentiment140&Message=$(text)


Scenario 2b (Text Not Already URL Encoded)

If you are using QVSource 1.2.4 or earlier or you are using text data from another source which is not already url encoded you will need script similar to the following to url encode the text before sending it to the sentiment analyser.

NOTE: URL encoding should be done using UTF-8.


Sub urlEncode(str)

	let str=replace(str, '%', '%25'); // should be first
	let str=replace(str, '#', '%23');
	let str=replace(str, ' ', '%20');
	let str=replace(str, '$', '%24');
	let str=replace(str, '&', '%26');
	let str=replace(str, '+', '%2B');
	let str=replace(str, ',', '%2C');
	let str=replace(str, '/', '%2F');
	let str=replace(str, '\', '%5C');
	let str=replace(str, ':', '%3A');
	let str=replace(str, ';', '%3B');
	let str=replace(str, '=', '%3D');
	let str=replace(str, '?', '%3F');
	let str=replace(str, '@', '%40');
	let str=replace(str, '[', '%5B');
	let str=replace(str, ']', '%5D');
	let str=replace(str, '>', '%3E');
	let str=replace(str, '<', '%3C');
	let str=replace(str, chr(10), '%0A'); 	// Line feed.
	let str=replace(str, chr(39), '%27');	// 39 Apostrophe
	
	call=str;
		
End sub

LET noRows = NoOfRows('Timeline');
   
for i=0 to $(noRows)-1
	
     let text = peek('text', $(i), 'Timeline');
     let textEncoded = text;
     call urlEncode(textEncoded);

     Sentiment:
     LOAD
    	'$(text)' as text, // Use this if you want to link your table on the text
    	status as sentiment_status,
    	score as sentiment_score
     FROM
     [http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Repustate&format=html&Message=$(textEncoded)]
     (html, utf8, embedded labels, table is @1);
next

Note - In the above script the SentimentEngine=Repustate which sentiment API to access.

If we wish to use one of the other sentiment APIs we can simply change this, for example:

http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_AlchemyAPI&Message=$(text)
http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Sentiment140&Message=$(text)


Caching

The sentiment analyser currently caches the sentiment scores. If you want to clear the cache follow the instructions here. This might be useful, for example, if the algorithm used to score the sentiment changes.

Backing Up The Cache

The cache will likely grow to a considerable size and retrieving items from the cache will be significantly faster than hitting the sentiment API. For this reason it is recommended that you take regular backups of this cache so that it can be restored in the future if necessary.

By default the cache should be found at the following location (unless you have changed it):
[INSTALLATION_DIR]\QVSource\Data\TextAnalyserV2\Cache

Change Log

1.1.3 - 08/04/16
  • Added EmotionAnalysis table to AlchemyAPI provider.

1.1.2 - 13/02/16
  • Fix to Repustate SentimentChunked table.

1.1.1 - 26/11/15
  • BREAKING CHANGES: The embedded API key for AlchemyAPI which was included in previous versions has now been removed.
  • Minor refactoring of web exception handling.

1.1.0 - 14/10/15
  • BREAKING CHANGES: In this version of the connector we have made major changes to allow this connector to run in the new Web Edtion of QVSource, you will need to make minor adjustments to your load scripts as described here -http://wiki.qvsource.com/Upgrading-to-version-1_1-of-the-Sentiment-Analysis-And-Text-Analytics-Connector.ashx. In this version the engine parameter has been removed and all tables (in the generated load URL/loadscript) of the engine included, e.g. Sentiment_Repustate, Sentiment_AlchemyAPI.
  • Added initial support for the MeaningCloud API.

1.0.9 - 08/10/15
  • Minor internal refactoring.

1.0.8 - 10/08/15
  • api parameter is no longer case sensitive.
  • Content type for Repustate now correctly set to application/x-www-form-urlencoded.

1.0.7 - 31/07/15
  • Moved to 3 digit version number in line with other connectors.

1.0.6.2 - 17/06/15
  • Polish now available as a language option in Repustate.
  • Fixed bug in Alchemy Sentiment table which errored if element was not present in AlchemyAPI response.

1.0.6.1 - 27/02/15
  • Fixed bug with Alchemy API key saving to the same settings key as the Repustate key.
  • Added mixed column to Alchemy API Sentiment table which shows 1 if the sentiment in the document is mixed.

1.0.6.0 - 05/02/15
  • Upgraded to V3 of the Repustate API.
  • BREAKING CHANGE: For Repustate the Adjectives, Nouns and Verbs tables have been removed. Please use the new PartsOfSpeech table.
  • Topics input now mandatory (Repustate SentimentByTopic).

1.0.5.1 - 06//14
  • POSSIBLE BREAKING CHANGE: There is no longer a 'built in' API Key for Repustate - you must register for and enter your own.
  • Russian option added for Repustate.

1.0.5.0 - 21/07/14
  • BREAKING CHANGE: Removed Sentimental (formerly Chatterbox) connectivity - this is because Sentimental have now removed their API from Mashape and as such we are no longer able to offer it either.

1.0.4.0 - 29/04/14
  • Entites, Expansions and Themes tables added to Repustate API.
  • Added full descriptions for Repustate tables.
  • Adding RegEx Replace table.

1.0.3.0 - 17/04/14
  • Added a regular expression parsing option.

1.0.2.0 - 08/10/13
  • More informative error message from Saplo connectivity in Text Analytics Connector V2 when establishing access token.
  • New SentimentByTopics table added to Repustate.
  • ChatterBox renamed to Sentimental (as per their rebranding).
  • Sentimental:
    • BREAKING CHANGE: Excitement detetcion removed (they removed this from the API, it is not a QVSource decision).
    • Anger detection for chinese added.

1.0.1.9 - 31/07/13
  • Alchemy API
    • tatus and statusInfo added to SentimentWithKeywords, Concepts and Keywords
    • statusInfo added to Language

1.0.1.8 - 15/07/13
  • Italian option added to Repustate.

1.0.1.7 - 24/06/13
  • Added a score column to the ChatterBox Sentiment table (sent * value).

1.0.1.6 - 13/06/13
  • ChatterBox API connectivity added (including Chinese, Multiple European languages as well as Anger and Excitement scoring).

1.0.1.5 - 28/04/13
  • Fixed bug where Sentiment140 language parameter was being ignored.
  • Fixed bug with batch sentiment analysis in Text Analytics Connector V2 connection to Repustate.

1.0.1.4 - 08/04/13
  • Added input to specify local Repustate server.
  • Fixed Repustate Usage table.

1.0.1.3 - 26/02/13
  • Fixed error of sentiment140 making https instead of http requests.
  • Added Categorise and Batch Sentiment Support to Repustate connector.

1.0.1.2 - 25/02/13
  • TwitterSentiment now renamed to Sentiment140.
  • Alchemy now uses https end point.
  • Repustate now uses https end point.

1.0.1.1 - 15/02/13
  • Saplo support now wired back in (an error caused this to be unwired in previous build).
  • Saplo now correctly advertises it's endpoint as being https.

1.0.1 - 07/02/13
  • AlchemyAPI and Random Support added.

1.0.0 - 23/11/12
  • Initial release.
  • Changes from previous version.
    • SentimentEngine parameter renamed to api.
    • Message parameter renamed to text.
  • Repustate version of Language parameter now named Repustate_Language.
  • Repustate now has a new DetectLanguage table.


(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