Navigation¶

For Everyone

For QlikView Users

For Advanced Users

For .NET Developers

If you like QVSource please consider showing your interest and support.

Search the wiki
»

Wiki Links

Create Account

Discuss (0) History Attachments

Twitter Connector for QlikView

RSS

Modified on 2012/05/14 16:56 by chrisb Categorized as connector, demo apps, social, Twitter

(10) » Sentiment Analysis & Text Analytics Connector For QlikView » Sharepoint Connector For QlikView » Twitter Connector for QlikView

Table of Contents [Hide/Show]

Schema
Usage
   Authentication
   Available Tables
   Using parameters in QlikView script
   Settings in UI
   Working with multiple accounts
   Working With Dates
   Using Entities
   UsersLookup Table
Demo Apps
    NEW! Twitter Social Media Engagement Checker
   TweetView
API Status
Search API Query Operators
Known Issues
Twitter Action URLs (Web Intents)
Change Log

This Connector loads data from the Twitter API into your QlikView application.

Schema¶

A large number of tables and fields are produced by the Twitter Connector. The following image displays as many as will fit on the QlikView structure view:

NOTE: The Twitter connector has now been expanded to include further tables and data not shown in this screen shot.

Usage¶

Authentication¶

On opening the connector you should see something similar to the following:

Pressing the "Authenticate" button begins authentication process.

Enter Twitter account user/email and password and click on "Authorize app" button.

If authorization is successful Twitter will return a PIN number. When the PIN appears press type the PIN in the textbox at the bottom left and press "Close button.

With this the authentication process is complete and the generated token and tokensecret keys will be filled in "Connector Settings" tab.
''These keys will be filled for the last authenticated account only! Previous one will be overwritten.''

Available Tables¶

Twitter connector expose several tables from the Twitter API:

HomeTimeline - list of the tweets that are viewed on twitter.com website (max 200 tweets are returned per call).
FriendTimeline - list of the tweets that exclude authenticated account tweets and re-tweets (max 200 tweets are returned per call).
Following - list of all users (and user details) that authenticated user is following.
Followers - list of all users (and user details) that follow authenticated user.
Search - return the last 1500 tweets for the searched term (for since_id parameter see next part). This table provide and extra return columns (entity_hashs, entity_mentions, entity_links and entity_source). Each column contain coma-separated list for users mentioned in the tweet, for hash tags and links contained in the tweet. (See the "Using Entities" part for more info how to use them). Fill the "Search term" text box in "Connector Settings" tag to view the result for entered term.
Be aware that the Search API is not complete index of all Tweets, but instead an index of recent Tweets. At the moment that index includes between 6-9 days of Tweets. So you cannot use the Search API to find Tweets older than about a week!
Trends - the top ten topics that are currently trending on Twitter.
RateLimit - remaining number of API requests available to the authenticated user before the API limit is reached for the current hour. Please be advised each twitter account by default have 350 API calls per hour. From the available tables only "RateLimit" itself doesn't infect the rate limit. The table will return the remaining API calls, total API calls per hour and the reset time. This table may be used at the top of your QlikView script to make sure that there is API calls left before load the data. Making this check will secure that QlikView reload will not fail. Below is an example script:


TwitterConnector_RateLimit:
LOAD
	value as RateLimit_value
FROM [http://localhost:5555/QVSource/DLS_TwitterConnector/?table=RateLimit] (txt, utf8, embedded labels, delimiter is '\t', msq)
WHERE
 type = 'remaining-hits';
 
let remainingHits = peek('RateLimit_value');
Drop Table TwitterConnector_RateLimit;
if $(remainingHits) > 10 then
// Place the main script here
end if

UsersLookup - return detailed info about users specified in "Users Lookup" textbox in main screen. The users must be comma-separated
UserFollowers - return detailed info about the followers of the user specified in "Username" textbox in main screen
UserFollowing - return detailed info about the users that user specified in "Username" textbox in main screen follow

''Please be aware that UserFollowers and UserFollowing tables consume 1 API call for each 100 users returned.''

Using parameters in QlikView script¶

QVSource provide the ability to pass parameters from QlikView load script. Accepted parameters are:

token/tokensecret - if this parameters are not provided the QVSource will read them from the settings file (the values in settings file are filled during authentication process and are encrypted in it)
searchTerm (used only in "Search" table) - specify the search terms for the search. Be advised that twitter will return and mentions(@) and hash tags(#) for the search term. For example searching for "qvsource" will return tweets where qvsource, @qvsource or #qvource pressent.
since_id (used only in "Search" table) - depending on QlikView application needs providing this parameter will force the search to return tweets with id bigger than provided one. Not specify this parameter will return all last tweets.
Request to QVSource using since_id parameter will look like this:
http://localhost:5555/QVSource/DLS_TwitterConnector/?table=Search&searchTerm='search_term'&since_id=10000000

Here is one possible method for getting the most recent 'since_id' from your QlikView data.


temp:
LOAD
Search_id AS Search_id_temp,
Search_created_at as Search_created_at_temp,
RESIDENT TwitterConnector_Search
order by Search_id desc

LET latestId = PEEK('Search_id_temp', 0, 'temp');
					
DROP TABLE temp;

A more sophisiticated version which assumes we have a search table containing both Tweets and Retweets for different search terms. This function should return the latest Tweet id for a certain search term and Retweet/Tweet classification - this will allow us to then run the same search again since the latest result for this search.


sub getLatestId(searchTerm, type)

	let latestId = '';
	
	if(not isnull(TableNumber('TwitterConnector_Search'))) then	
		qualify *;
		temp:
		LOAD
		Search_id,
		SearchTerm,
		TweetType,
		RESIDENT TwitterConnector_Search
		where SearchTerm='$(searchTerm)' and TweetType='$(type)' 
		order by Search_id desc;
		unqualify *;
		
		let latestId = PEEK('temp.Search_id', 0, 'temp');
		
		call=latestId;
				
		DROP TABLE temp;
			
	endif

endsub

Settings in UI¶

Max return pages for Home/Friends timeline - for timelines twitter may return max 10 pages of data. Specifying lower than 10 will limit the returned records (it maybe useful for testing purposes)
Max return pages for Search - same as the timeline but for Search API and here the max limit of pages is 15
Return Fixed Columns - when you get the QV script for some of the tables for the first time this will not guarantee that the columns will be the same from now on. Some columns appear only if there is data for them (very rare columns). So in the next requests some of the columns may not exists and QV reload will fail. Checking this checkbox will guarantee that the columns will be the same on every request. QV Source internally keep a list with columns for each table and if someone is missing from the request it will automatically add it.

Working with multiple accounts¶

As mentioned before the authentication process stores token/tokensecret keys only for the last authenticated user. If there is need more than user to be used to call the API (for example to have two QlikView apps that analyze the data for two accounts) token/tokensecret keys can be provided as a QlikView script parameter described in "Using parameters in QlikView script" above.

Working With Dates¶

Twitter returns dates in the following format:

Sat, 12 May 2012 19:48:17 +0000

In other words timestamps relative to GMT/UT. This is one way in which you can parse these timestamps so that they are natively recognised by QlikView:

Timestamp#(SubField(Search_created_at, ', ', 2), 'DD MMM YYYY hh:mm:ss +0000') as Search_created_at_timestamp

Note however that these Tweets will still be relative to GMT/UT and may look strange if displayed in your user application. One way to convert these to your local timezone you could use something like this:

let vLocalTimeZone = 'Paris';
...
ConvertToLocalTime(Timestamp#(SubField(Search_created_at, ', ', 2), 'DD MMM YYYY hh:mm:ss +0000'), '$(vLocalTimeZone)') as Search_created_at_timestamp_local

According to the QlikView documentation the following are valid values for the local time zone variable:

Valid places and time zones:

Abu Dhabi, Adelaide, Alaska, Almaty, Amsterdam, Arizona, Astana, Athens, Atlantic Time (Canada), Auckland, Azores, Baghdad, Baku, Bangkok, Beijing, Belgrade, Berlin, Bern, Bogota, Brasilia, Bratislava, Brisbane, Brussels, Bucharest, Budapest, Buenos Aires, Cairo, Canberra, Cape Verde Is., Caracas, Casablanca, Central America, Central Time (US & Canada), Chennai, Chihuahua, Chongqing, Copenhagen, Darwin, Dhaka, Eastern Time (US & Canada), Edinburgh, Ekaterinburg, Fiji, Georgetown, Greenland, Greenwich Mean Time : Dublin, Guadalajara, Guam, Hanoi, Harare, Hawaii, Helsinki, Hobart, Hong Kong, Indiana (East), International Date Line West, Irkutsk, Islamabad, Istanbul, Jakarta, Jerusalem, Kabul, Kamchatka, Karachi, Kathmandu, Kolkata, Krasnoyarsk, Kuala Lumpur, Kuwait, Kyiv, La Paz, Lima, Lisbon, Ljubljana, London, Madrid, Magadan, Marshall Is., Mazatlan, Melbourne, Mexico City, Mid-Atlantic, Midway Island, Minsk, Monrovia, Monterrey, Moscow, Mountain Time (US & Canada), Mumbai, Muscat, Nairobi, New Caledonia, New Delhi, Newfoundland, Novosibirsk, Nuku'alofa, Nuku´alofa, Osaka, Pacific Time (US & Canada), Paris, Perth, Port Moresby, Prague, Pretoria,Quito, Rangoon, Riga, Riyadh, Rome, Samoa, Santiago, Sapporo, Sarajevo, Saskatchewan, Seoul, Singapore, Skopje, Sofia, Solomon Is., Sri Jayawardenepura, St. Petersburg, Stockholm, Sydney, Taipei, Tallinn, Tashkent, Tbilisi, Tehran, Tijuana, Tokyo, Ulaan Bataar, Urumqi, Warsaw, Wellington, West Central Africa, Vienna, Vilnius, Vladivostok, Volgograd, Yakutsk, Yerevan or Zagreb.

Also GMT, GMT-01:00, GMT+04:00 etc. are valid places.

NOTE that we have found the GMT+01:00 style notation doesn't work on our systen whereas UTC+01:00 style notation does - however we have also experienced odd behaviour with this.

Using Entities¶

QVSource Twitter Connector add few extra columns to the original returned data - entity_hash, entity_mentions, entity_link and entity_source. Except entity_source the other columns contain comma separated values for hash tags appeared in tweet, user mentions and links. entity_source column contain the name of the tool used to publish the tweet. For example: web, hootsuite, twitter for android etc. The following QlikView script shows how returned entities are used to produce a separate table linked to main tweet table through "ID"" field.


Load
	ID,
	'Hash' as EntityType,
	subfield(entity_hashs,',') as entity
Resident TwitterConnector_Search
where entity_hashs <> '';

concatenate

Load
	ID,
	'Mention' as EntityType,
	subfield(entity_mentions,',') as entity
Resident TwitterConnector_Search
where entity_mentions <> '';

concatenate

Load
	ID,
	'Link' as EntityType,
	subfield(entity_links,',') as entity
Resident TwitterConnector_Search
where entity_links <> '';

If the tweet is: "Started using #qvsource today! @qvsource #qlikview http://www.qvsource.com" the return data in entity table will be:

ID	EntityType	entity
1	Hash	qvsource
1	Hash	qlikview
1	Mention	qvsource
1	Link	http://www.qvsource.com

The loaded tables in QlikView will be:

UsersLookup Table¶

The UsersLookup table allows you to get additional information about a Twitter user (it calls through to https://dev.twitter.com/docs/api/1/get/users/lookup in the background.

This part of the Twitter API will accept up to 100 account names in once request and this is the most efficient way of using this table also in QVSource. The following script illustrates how you can combine a number of Twitter names into a single request.


let users = fieldvaluecount('Search_from_user');
let concatenated = fieldvalue('Search_from_user',1);
let currentCount = 1;

for i=2 to $(users)

	let sep = ',';
	
	if(concatenated = '') then
		let sep = '';
	endif
	
	let concatenated = concatenated & sep & fieldvalue('Search_from_user',$(i));
	let	currentCount = currentCount + 1;

	if(currentCount >= 99 or i=$(users)) then
		
		TwitterConnector_UsersLookup:
		LOAD
		    name as UsersLookup_name,	    
		    id as UsersLookup_id,
		    screen_name as Search_from_user,
		    location as UsersLookup_location,
		    description as UsersLookup_description,
		    profile_image_url as UsersLookup_profile_image_url,
		    profile_image_url_https as UsersLookup_profile_image_url_https,
		    url as UsersLookup_url,
		    protected as UsersLookup_protected,
		    followers_count as UsersLookup_followers_count,
		    profile_background_color as UsersLookup_profile_background_color,
		    profile_text_color as UsersLookup_profile_text_color,
		    profile_link_color as UsersLookup_profile_link_color,
		    profile_sidebar_fill_color as UsersLookup_profile_sidebar_fill_color,
		    profile_sidebar_border_color as UsersLookup_profile_sidebar_border_color,
		    friends_count as UsersLookup_friends_count,
		    created_at as UsersLookup_created_at,
		    favourites_count as UsersLookup_favourites_count,
		    utc_offset as UsersLookup_utc_offset,
		    time_zone as UsersLookup_time_zone,
		    profile_background_image_url as UsersLookup_profile_background_image_url,
		    profile_background_image_url_https as UsersLookup_profile_background_image_url_https,
		    profile_background_tile as UsersLookup_profile_background_tile,
		    profile_use_background_image as UsersLookup_profile_use_background_image,
		    notifications as UsersLookup_notifications,
		    geo_enabled as UsersLookup_geo_enabled,
		    verified as UsersLookup_verified,
		    following as UsersLookup_following,
		    statuses_count as UsersLookup_statuses_count,
		    lang as UsersLookup_lang,
		    contributors_enabled as UsersLookup_contributors_enabled,
		    follow_request_sent as UsersLookup_follow_request_sent,
		    listed_count as UsersLookup_listed_count,
		    show_all_inline_media as UsersLookup_show_all_inline_media,
		    default_profile as UsersLookup_default_profile,
		    default_profile_image as UsersLookup_default_profile_image,
		    is_translator as UsersLookup_is_translator,
		    status_created_at as UsersLookup_status_created_at,
		    status_id as UsersLookup_status_id,
		    status_text as UsersLookup_status_text,
		    status_source as UsersLookup_status_source,
		    status_truncated as UsersLookup_status_truncated,
		    status_favorited as UsersLookup_status_favorited,
		    status_in_reply_to_status_id as UsersLookup_status_in_reply_to_status_id,
		    status_in_reply_to_user_id as UsersLookup_status_in_reply_to_user_id,
		    status_in_reply_to_screen_name as UsersLookup_status_in_reply_to_screen_name,
		    status_retweet_count as UsersLookup_status_retweet_count,
		    status_retweeted as UsersLookup_status_retweeted,
		    status_geo as UsersLookup_status_geo,
		    status_coordinates as UsersLookup_status_coordinates,
		    status_place as UsersLookup_status_place,
		    status_contributors as UsersLookup_status_contributors
		FROM
		[http://localhost:5555/QVSource/DLS_TwitterConnector/?table=UsersLookup&lookupusers=$(concatenated)]
		(txt, utf8, embedded labels, delimiter is '\t', msq); 
		
		let concatenated = '';
		let currentCount = 0;
		
	endif
	
next

Demo Apps¶

There are two Twitter demo apps that are now available

NEW! Twitter Social Media Engagement Checker¶

This application uses several QVSource connectors and has been designed so that you can easily change the Twitter accounts and search terms of interest.

To find out more, click here.

Summary page:

TweetView¶

TweetView app originally was made by calling the Search API itself from inside QlikView. With this version the script is changed to use QVSource as data provider engine.
TweetView (QVSource edition)

Main view:

API Status¶

Twitter API Status

Search API Query Operators¶

Below are official query operators from Twitter API documentation:

Example	Description
twitter search	containing both "twitter" and "search". This is the default operator
"happy hour"	containing the exact phrase "happy hour"
love OR hate	containing either "love" or "hate" (or both)
beer -root	containing "beer" but not "root"
#haiku	containing the hashtag "haiku"
from:twitterapi	sent from the user @twitterapi
to:twitterapi	sent to the user @twitterapi
place:opentable:2	about the place with OpenTable ID 2
place:247f43d441defc03	about the place with Twitter ID 247f43d441defc03
@twitterapi	mentioning @twitterapi
superhero since:2011-05-09	containing "superhero" and sent since date "2011-05-09" (year-month-day).
twitterapi until:2011-05-09	containing "twitterapi" and sent before the date "2011-05-09".
movie -scary :)	containing "movie", but not "scary", and with a positive attitude.
flight :(	containing "flight" and with a negative attitude.
traffic ?	containing "traffic" and asking a question.
hilarious filter:links	containing "hilarious" and with a URL.
news source:tweet_button	containing "news" and entered via the Tweet Button

There is also a useful article entitled How to Become a Twitter Search Ninja.

Known Issues¶

A long standing bug in the Twitter search API means the from user id returned by the search does not match the actual Twitter user's id. So you should use the Twitter username/screen name to link tables wherever possible.

Twitter Action URLs (Web Intents)¶

It's possible to use Twitter URLs to respond to Tweets or follow users. These can be constructed in your QlikView application, which when clicking launch Twitter on the appropriate page. The following table shows the URL, with token to replace, for the popular actions

Action	URL
Reply to a Tweet	http://twitter.com/intent/tweet?in_reply_to=[TWEET_ID]
Retweet a Tweet	http://twitter.com/intent/retweet?tweet_id=[TWEET_ID]
Favourite a Tweet	http://twitter.com/intent/favorite?tweet_id=[TWEET_ID]
Short User Profile, with option to Follow	http://twitter.com/intent/user?screen_name=[SCREEN_NAME]

You can read more about Twitter Web Intents here.

Change Log¶

1.1.5 - 19/04/2012

Minor update to parsing of max number of page parameters at bottom of config interface. Should not affect behaviour.

1.1.4 - 08/03/2012

OAuth endpoints changed from http://twitter.com/oauth to https://api.twitter.com/oauth (former deprecated on May 14th 2012).
max Timeline Pages, Search Pages, mentions pages and user timeline pages now default to 1.

1.1.3 - 31/01/2012

Added check in for search table to prevent more than 15 pages of results from being requested.

1.1.2 - 04/01/2012

Added Since ID parameter to UI to only return Tweets since a certain Tweet. This was available already as a url parameter.
Major refactoring of code base, should not have any significant functional change other than minor performance improvement but please notify us if you experience issues.
Slight rearrangement of UI.
UsersLookup table renamed UserLookup
UserLookupByID table added
UserFollowerIDs table added.
Bug in UserFollowers table fixed where it would only return the first 5000 followers.
Can now specify &result_type=mixed|recent|popular in th url request to the search table (there is not yet an equivalent option the in the QVSource user interface.
Search table should now return the same columns when no results are found (as long as the 'return fixed columns' is checked.
Minor change to contents logged when API call tracking is turned on.
Added RetweetedByUser tables.
Now written to allow multithreaded access (e.g. from multiple QlikView documents reloading concurrently).

1.1.1 - 13/12/2011

Added "username" parameter to UserTimeline table to get timeline for other user
Added "includert" parametere to UserTimeline table
GUI changes to reflect new parameteres

1.1.0 - 06/12/2011

Fixed "entity_link" column data (in all tables) to not be lowered
Added "DirectMessages" table
Added "UserTimeline" table
Added "Mentions" table
(GUI) Added "Search lang" textbox which will filter the stream for specific language
(GUI) Added "Return DM" combobox which will filter the DirectMessages table (All, Send, Received)
(GUI) Added "Include RT" chechbox for Mentions table to include/exclude RT from result
(GUI) Added Max return pages for UserTimeline table field
(GUI) Added Max return pages for Mentions table field