Welcome Guest, you are in: Login

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

This page illustrates a sample QlikView load script which performs a Twitter search (with new results accumulated into a QVD file) and then runs the Tweets through the Saplo API via the QVSource Sentiment Analysis and Text Analytics Connector.

The sentiment results are also accumulated into a QVD file meaning that the minumum number of calls to the Saplo API are made (a given Tweet ID should only be scored the first time).

PLEASE NOTE that the Saplo sentiment API is in private beta and you will need to contact Saplo for both an API key and for permission to access this API. You will also need QVSource 1.2.4.27 or later.

Schema

Note that in the Schema the Saplo_Predict and SearchResults tables are stored to QVDs. The other tables could actually be dropped from the model at the end of the load script.

Image

Usage

In order to use this script you will need to have:

  • QVSource 1.2.4.27 or later.
  • A Saplo API Key and Secret Key. You can sign up here. Please mention QVSource when doing so.
  • Access to Saplo Predict functionality and a model ID for the prediction model. You should also contact Saplo for this.
  • A collection id (see below).

Collection ID

To see the collections already available to you just click the ListCollections table.

Image

If you need to create a new one simply enter a collection name on the config tab for the connector:

Image

And then on the 'Connector Data' tab click the CreateCollection button:

Image

The Script

At this point you should have a collection id and model id which you will need to enter into the load script below.

You will also need to configure the search term - in this example we are running a search for #obama as the sentiment model we are running is trained for political Tweets.

The script is broken into two tabs:

Search Tab


//
// Config
//
[UPDATE THE FOLLOWING LINE WITH YOUR SEARCH TERM]
let vSearchTerm = '%23obama'; // url encoded version of #obama
let vSaploModelId = '[INSERT YOUR MODEL ID HERE]';
let vSaploCollectionId = '[INSERT YOUR COLLECTION ID HERE]';

let qvd = 'SearchResults.qvd';

let size = filesize('$(qvd)');

if not isnull(size) then

	SearchResults:
	LOAD
	*
	FROM '$(qvd)' (qvd);
	
	temp:
	load
	SearchV2_id as temp_id,
	SearchV2_SearchTerm as temp_SearchTerm
	Resident
	SearchResults
	where SearchV2_SearchTerm = '$(vSearchTerm)'
	order by SearchV2_id desc;

	let vSinceId = peek('temp_id', 0, 'temp');

	drop table temp;
	
ELSE

	let vSinceId = '';
		
end if


SearchResults:
LOAD
	//
	// Manually added
	//
	'$(vSearchTerm)' as SearchV2_SearchTerm,
	timestamp(now(1)) as SearchV2_loaded_timestamp,
	//
	// From connector
	//
	id as SearchV2_id,
	created_at as SearchV2_created_at,
	Timestamp#(SubField(created_at, ', ', 2), 'DD MMM YYYY hh:mm:ss +0000') as SearchV2_created_at_timestamp,
	text as SearchV2_text,
	text_urlEncoded as SearchV2_text_urlEncoded,
	from_user as SearchV2_from_user,
	from_user_id as SearchV2_from_user_id,
	from_user_name as SearchV2_from_user_name,
	to_user as SearchV2_to_user,
	to_user_id as SearchV2_to_user_id,
	to_user_name as SearchV2_to_user_name,
	geo_type as SearchV2_geo_type,
	geo_latititude as SearchV2_geo_latititude,
	geo_longitude as SearchV2_geo_longitude,
	source as SearchV2_source,
	iso_language_code as SearchV2_iso_language_code,
	metadata_result_type as SearchV2_metadata_result_type,
	profile_image_url as SearchV2_profile_image_url,
	profile_image_url_https as SearchV2_profile_image_url_https,
	in_reply_to_status_id as SearchV2_in_reply_to_status_id,
	hashtag_count as SearchV2_hashtag_count,
	hash_tags as SearchV2_hash_tags,
	user_mentions_count as SearchV2_user_mentions_count,
	user_mentions_screennames as SearchV2_user_mentions_screennames,
	url_count as SearchV2_url_count,
	expanded_urls as SearchV2_expanded_urls,
	media_count as SearchV2_media_count,
	media_expanded_urls as SearchV2_media_expanded_urls,
	media_photo_count as SearchV2_media_photo_count,
	media_photo_urls as SearchV2_media_photo_urls
FROM
[http://localhost:5555/QVSource/TwitterConnector/?table=SearchV2&searchTerm=$(vSearchTerm)&searchmaxpages=15&deletefromcache=true&since_id=$(vSinceId)&format=qvx]
(qvx);
// might want to to a where not exists double check here based on a key combination of search id and search term although the since id should prevent duplicates.

store SearchResults into '$(qvd)';

Sentiment Tab


let qvdSaplo = 'Saplo_Sentiment.qvd';

let size = filesize('$(qvdSaplo)');

if not isnull(size) then

	Saplo_Predict:
	LOAD
	*
	FROM '$(qvdSaplo)' (qvd);
	
	Saplo_ScoredAndUnscoredTweets:
	LOAD
	SearchV2_id as Saplo_ScoredAndUnscoredTweets_id,
	SearchV2_text_urlEncoded as Saplo_ScoredAndUnscoredTweets_text_urlEncoded
	resident SearchResults;

	outer join load
	SearchV2_id as Saplo_ScoredAndUnscoredTweets_id,
	Predict_value as Saplo_ScoredAndUnscoredTweets_Predict_value
	resident
	Saplo_Predict;

	Saplo_StillToScore:
	load distinct
	Saplo_ScoredAndUnscoredTweets_id as Saplo_StillToScore_id,
	Saplo_ScoredAndUnscoredTweets_text_urlEncoded as Saplo_StillToScore_text_urlEncoded//,
	resident Saplo_ScoredAndUnscoredTweets
	where isnull(Saplo_ScoredAndUnscoredTweets_Predict_value);
	
	drop table Saplo_ScoredAndUnscoredTweets;
	
else	
	
	Saplo_StillToScore:
	load distinct
	SearchV2_id as Saplo_StillToScore_id,
	SearchV2_text_urlEncoded as Saplo_StillToScore_text_urlEncoded
	resident SearchResults;
	
end if

LET noRows = NoOfRows('Saplo_StillToScore');

for i=0 to $(noRows)-1

	let id = peek('Saplo_StillToScore_id',$(i),'Saplo_StillToScore');
	let text = peek('Saplo_StillToScore_text_urlEncoded',$(i),'Saplo_StillToScore');
	
	Saplo_AddText:
	LOAD
		'$(id)' as SearchV2_id,
		'$(text)' as AddText_text,
		status as AddText_status,
		headline as AddText_headline,
		ext_text_id as AddText_ext_text_id,
		collection_id as AddText_collection_id,
		publish_date as AddText_publish_date,
		text_id as AddText_text_id,
		url as AddText_url
	FROM
	[http://localhost:5555/QVSource/SentimentAnalyser/?table=AddText&SentimentEngine=Saplo&Saplo_collection_id=$(vSaploCollectionId)&Message=$(text)&format=qvx]
	(qvx);
	
next

Saplo_StillToScore_WithAddedStatusOK:
load
SearchV2_id as idToScore,
AddText_text_id as saploIdToScore
resident
Saplo_AddText
where
AddText_status = 'OK';

Saplo_TextWhichCouldNotBeAdded:
load
AddText_status as Saplo_TextWhichCouldNotBeAdded_Status,
SearchV2_id as Saplo_TextWhichCouldNotBeAdded_id,
AddText_text as Saplo_TextWhichCouldNotBeAdded_text
resident
Saplo_AddText
where
AddText_status <> 'OK';

LET noRows = NoOfRows('Saplo_StillToScore_WithAddedStatusOK');

for i=0 to $(noRows)-1

	let id = peek('idToScore',$(i),'Saplo_StillToScore_WithAddedStatusOK');
	let saploId = peek('saploIdToScore',$(i),'Saplo_StillToScore_WithAddedStatusOK');
		
	Saplo_Predict:
	LOAD
		'$(id)' as SearchV2_id,
		status as Predict_status,
		collection_id as Predict_collection_id,
		target_word as Predict_target_word,
		model_id as Predict_model_id,
		value as Predict_value,
		if(value = 'negative', -1, if(value = 'neutral', 0, 1)) as sentiment,
		text_id as Predict_text_id
	FROM
	[http://localhost:5555/QVSource/SentimentAnalyser/?table=Predict&SentimentEngine=Saplo&Saplo_collection_id=$(vSaploCollectionId)&Saplo_text_id=$(saploId)&Saplo_model_id=$(vSaploModelId)&format=qvx]
	(qvx);
	
next

store Saplo_Predict into 'Saplo_Sentiment.qvd';

Possible Enhancements

This script is intended as a sample only - we recommend you take the time to understand how it works and also point out any potential issues you find. There are a number of possible enhancements which could be made to this script which have already been identified:
  • It does not currently check if the Saplo API quota limit for the API key is exceeded, when this happens the calls to AddText and Predict will start failing. The script could include regular calls to the Account table to check if there are still calls remaining.
  • A Saplo collection (to which text must be added before sentiment prediction is achieved) can only be in one language and in this demo some texts not in English fail in the AddText call with a message similar to 'Error returned from Saplo API - 1206 : Text language (fr) and collection language (en) don't match.'. This search to Twitter could be tweaked to only request English Tweets.
  • As with our other Twitter demos, you might want a table of search terms and a script which loops through each one to build up a more complete SearchResults 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