Welcome Guest, you are in: Login

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

We are now working on a new V3 of this Connector.


Demo Application(s)

The Facebook Demo Applications are currently being updated to work with this new version of the connector.

Using the Connector

The Facebook for Pages connector allows you to get many different data sets from Facebook Fan Pages and groups (public only), for example feed items, comments for feeditems, likes for items, information on users and more.

Please note that we are only able to return the data provided by the Facebook graph API - any discrepancies are due to the Facebook graph API and the data it returns.

Upgrading from V1

You will need to upgrade your load script to use this new version - this is straightforward and described here.

Tables

The new connector makes the tables more accessible and easier to understand which parameters are needed to run them.

There are over 25 tables, including:
  • FeedItems
  • Post
  • Posts
  • CommentsForItem
  • Milestones

You can see all the tables available listed in the top left corner of the connector's panel.

Image

On selecting a table, the area below displays some relevant notes that may provide a better insight into using the table, as shown in the image above.

Selecting a table also changes the area to the right of the table list. This area is used to enter, where necessary, additional information that's needed to run the table.

So, in the example below, you can see that by selecting the FeedItems table, you must (as depicted by the *) enter the Facebook Page of interest. Optionally, you can also specify a start date and the maximum number of items to return.

Image

Authentication

Before you can start to use the connector to extract data you must authenticate QVSource with Facebook. Do this by selecting any of the tables (other than API Status) and clicking 'authenticate'

Image

Next, enter your Facebook credentials on the subsequent screen and follow the instructions. Once you have done this you should see your token appear as well as an expiry date for the token:

Image

NOTE: Unfortunately the maximum time which a token can be acquired for is 60 days. It is important that you re-authenticate with the connector before this date to ensure that you continue to get data. We would recommend setting a calendar alert.

Extracting Data

Once you have authenticated you can start extracting data. Select the table you wish to load and fill in the mandatory fields (denoted with a *) and the click the 'Run' button at the bottom. Please see the section below (Finding IDs) for more information about IDs.

Image

You can now go to the 'QlikView Load Script' tab and copy and paste the load script into your QlikView application.

Image

To see how you can use the generated QlikView in a very flexible way, please read the section Working with the QlikView Script below.

Finding IDs

Depending on the table you are looking to extract data from, there are normally 1 of 3 IDs that you'll need to enter.

These are:
  • page or group ID or Name
  • object ID
  • user ID

As you'll see in the next sections these are somewhat hierarchical, so once you have identified a page (and it's page id) you can then look at the objects on that page (and get the object ids) and for each of those objects you can see who's interacted with them through 'liking' or commenting, from which you can find their id (the user id).

Facebook Fan Page or Group Name/Id

Some tables such as FeedItems and Milestones require you to enter the Page or Group's Name or ID.

To find this, browse to the Facebook Page that you wish to access. If the Facebook page has a 'vanity' URL, like this:

Image

The you should enter this value, 'cocacola' into the text box.

Otherwise it will probably look something more like the following, in which case you should enter the highlighted numeric identifier into QVSource:

Image

Object ID

This ID represents a number of 'objects' that you might find on a Facebook page, such as a post, comment or milestone. To find these often you'll have to run a table that returns items on a page, for example the Milestones table takes the Page's ID and returns all the feeditems, which represent the milestones.

Each of these feeditems have a unique feeditem id, which you can find by looking at the data returned.

So, to continue the example, on running the Milestones table for the page, 'qlik', we have all the feeditems and we can see that the second column shows the feeditem_id

Image

We can now copy one of the feeditem id's

Image

And use it in say the LikesForItem table, which requires an ObjectID. So, paste in the id's value and press 'Run' along the bottom

Image

The table on the right now shows all the users that like that particular Milestone.

User ID

This is the ID that represents a Facebook User.

Following on from the above example, on running the LikesForItem table all the users that like a Milestone were returned. The first column is the User ID for each user.

So, we can now select one of these

Image

and use it on the User table to find out more about that particular user

Image

Working with the QlikView Script

Finding QVSource Parameters in the QlikView Script

When entering details for a specific table, these will become part of the QlikView script that QVSource generates.

For example, when you look for a particular Facebook page, it's name or ID will form part of the LOAD FROM statement at of the script.

Image

If we'd have entered the start from date or maximum number of items, these would also have appeared here.

Image

In general the configuration information entered in the QVSource UI is converted into parameters in the load script so they can be modified during the reload process. This is part of what makes QVSource so powerful!

Using Facebook Tables Together

This section will show you how to build a very simple QlikView application using 2 tables together.

In getting data from a Facebook table, you can then use information that's returned to then use in another table.

Carrying on from the previous example, on running a page for it's feeditems, we see all the items and their respective IDs.

Image

Going to the QlikView Script tab, we can copy and paste this into a QlikView application (you can see the parameters highlighted)

Image

Going back to QVSource, on selecting one of the feeditem id's and copying it, we can paste it in say the LikesForItem table and run the script to find out who's liked the item we previously selected

Image

Looking at the QlikView script that QVSource generates, we can see the feeditem's ID

Image

We can now copy the QlikView script for this into the QlikView application.

The following using these 2 tables together. The first load statement grabs the most recent 10 feed items, the second loops through each of the items setting the feeditem id to a variable and uses this in the LikesForItem tabel to identify users that has liked it:


SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

FacebookFanPagesConnectorV2_FeedItems:
LOAD
	page as page,
	feeditem_id as feeditem_id,
	created as FeedItems_created,
	timestamp#(mid(created, 1, 10) & mid(created, 12, 8), 'YYYY-MM-DDhh:mm:ss') as FeedItems_created_qvdatetime,
	date#(subfield(created, 'T', 1), 'YYYY-MM-DD') as FeedItems_created_qvdate,
	time#(subfield(created, 'T', 2), 'hh:mm:ss+0000') as FeedItems_created_qvtime,
	updated as FeedItems_updated,
	timestamp#(mid(updated, 1, 10) & mid(updated, 12, 8), 'YYYY-MM-DDhh:mm:ss') as FeedItems_updated_qvdatetime,
	date#(subfield(updated, 'T', 1), 'YYYY-MM-DD') as FeedItems_updated_qvdate,
	time#(subfield(updated, 'T', 2), 'hh:mm:ss+0000') as FeedItems_updated_qvtime,
	poster_id as poster_id,
'
' some fields removed for clarity
'
	shares_count as FeedItems_shares_count,
	comments_count as FeedItems_comments_count,
	has_comments as FeedItems_has_comments,
	has_at_least_this_many_comments as FeedItems_has_at_least_this_many_comments
FROM
[http://localhost:5555/QVSource/FacebookFanPagesConnectorV2/?table=FeedItems&appID=&FacebookPageOrId=qlik&MaxNumberOfItems=10]
(qvx);

LET noRows = NoOfRows('FacebookFanPagesConnectorV2_FeedItems');
     
for i=0 to $(noRows)-1 // loop through every row
     
    let feedID = peek('feeditem_id', $(i), 'FacebookFanPagesConnectorV2_FeedItems');
                 

	FacebookFanPagesConnectorV2_LikesForItem:
	LOAD
		id as feeditem_id,
		category as LikesForItem_category,
		name as LikesForItem_name
	FROM
	[http://localhost:5555/QVSource/FacebookFanPagesConnectorV2/?table=LikesForItem&appID=&ObjectId=$(feedID)&MaxNumberOfItems=10]
	(qvx);

NEXT

This technique can be applied to link tables together and is commonly used with many of the QVSource Connectors.

Clearing the Cache

It is important to note that this connector currently caches the results of a particular request indefinitely.

To prevent QVSource from using the cache, you can simply include the noCache=true parameter in your requests.

Please read this page for general information on clearing the cache. However in general the cache is cleared using the following script:


LOAD
QVSource_ClearCache_Result,
QVSource_ClearCache_SearchPattern,
QVSource_ClearCache_NoItemsDeleted
FROM
[http://localhost:5555/QVSource/FacebookFanPagesConnectorV2/?clearcache=true&clearcache_searchPattern=SEARCH_PATTERN]
(txt, utf8, embedded labels, delimiter is '\t', msq); 

For this Facebook connector the following search patterns are useful.

Pages and Feeds

SEARCH_PATTERN = [page_name]_Page: This will clear the cached Page table for this Facebook site.
SEARCH_PATTERN = [page_name]_Feed*: This will clear the cached Feed table for this Facebook site. Note the star here is because the table is cached as a number of paged files (e.g. [page_name]_Feed_1_100, [page_name]_Feed_2 etc.). SEARCH_PATTERN = [page_name]_posts*: This will clear the cached Posts table for this Facebook site. Note the star here is because the table is cached as a number of paged files (e.g. [page_name]_posts_1_100, [page_name]_Feed_2 etc.).

From the above then we can also clear both sets of tables using SEARCH_PATTERN = [page_name]*.

Users

SEARCH_PATTERN = User_[userdid]: This will delete the cached User table for the specific user. SEARCH_PATTERN = User*: This will delete the cached User table for all users.

Comments

SEARCH_PATTERN = [object id]_comments: This will delete the cached comments for a particular object (e.g. facebook post).
SEARCH_PATTERN = *_comments*: This will delete all cached comments stored by QVSource.

Searches

In QVSource 1.5.9.5 and later, search results are no longer cached by QVSource.

In earlier versions - the following applies:
SEARCH_PATTERN = *_search_post_*: This will delete all cached searches for posts. The wild card at the front is because the actual key for the cached item will be [hash of search term]_search_post_[page number] so this is really the only way to clear all the cached search results.
SEARCH_PATTERN = *_search_page_*: This will delete all cached searches for pages. The wild card at the front is because the actual key for the cached item will be [hash of search term]_search_page_[page number] so this is really the only way to clear all the cached search results.
SEARCH_PATTERN = *_search_user_*: This will delete all cached searches for posts. The wild card at the front is because the actual key for the cached item will be [hash of search term]_search_user_[page number] so this is really the only way to clear all the cached search results.

Common Questions

  • Can I get the list of users who liked my fan page?
    • Unfortunately this is not currently supported by the Facebook Graph API and so is not something which we can support yet in QVSource either.

API Status

APIs are not error free or always 100% available. If you are having issues with the connector please first check that there are no problems at the API end point. http://developers.facebook.com/live_status/

You should also read this page which explains some quirks which you might find when using this connector.

Also - if you are experiencing an issue with the connector (for example after it was previously working ok) you might also try searching on the Facebook bug tracker for recent bugs which might explain the experience you are having. This will probably be the first place we will look after checking the API status.

Troubleshooting

Inconsistent Like Count Data

It appears that sometimes Facebook returns a different overall like count value to the actual number of likes it will list for a particular object, e.g. a post. For example, for the QVSource fan page we located a post which lists 7 likes for a particular post (by counting the rows):

Image

However the LikeCountForItem table shows 8:

Image

This is actually not a bug in QVSource, as we can see from locating the post on Facebook, it also shows a like count of 8 but only lists 7:

Image

It might be this is due to Facebook not always keeping the total count updated. For example, if somebody likes your post then subsequently unlikes it, this may take some time to get updated or perhaps there is a Facebook bug.

Change Log

1.1.1 - 04/02/16
  • This has now been tagged as deprecated as we have started work on a new V3 of this Connector. There is no intention to remove this connector in the next 6 months, tagging as deprecated at this stage is primarily intended to encourage any new users of the product to start with the new V3 of the Connector.

1.1.0 - 07/01/16
  • Added more helpful error message when connector not authenticated.

1.0.9 - 07/12/15
  • Minor refactoring of web exception handling.

1.0.8 - 17/08/2015
  • BREAKING CHANGE Removed the GroupMembers table (unfortunately we no longer have permission to make this API request (https://developers.facebook.com/docs/graph-api/reference/v2.4/group/members) as it requires the user_groups permission (https://developers.facebook.com/docs/facebook-login/permissions/v2.2) which is only 'granted to apps building a Facebook-branded client on platforms where Facebook is not already available'.)

1.0.7 - 03/08/2015
  • For certain types of errors returned from the Facebook API, the connector will now wait a few seconds and retry the request a second time. This will also be logged.

1.0.6 - 23/05/2015
  • Updated some parameter labels to clarify that only public groups can be used.

1.0.5 - 11/05/2015
  • Added a Max number of calls per second input parameter to (almost) all tables. It is difficult to find official documentation on the API call limits but some sources suggest a maximum of 600 calls per 600 seconds. If you are experiencing issues you could try setting this parameter although this will likely significantly slow down your reload speeds.

1.0.4 - 21/04/2015
  • Fixed bug in CommentsAndRepliesForItem table.

1.0.3 - 06/04/2015
  • Upgraded authentication code to use new framework ready for QVSource Web Edition.
  • Results of Search tables are no longer cached by the connector.
  • BREAKING CHANGE: We have removed the MyAccounts and Messages table which do not appear to be being used and would require us to submit the app to Facebook for approval of the manage_pages permission.
  • BREAKING CHANGE: We have removed the FriendsWhoUseThisApp table which does not appear to be useful.

1.0.2 - 10/01/2015
  • Updated/corrected list of advertised/required HTTP end points.

1.0.1 - 16/09/2014
  • Fixed bug with 'Don't Return Items Before Date X' feature which was only checking the updated_time after the first batch of items returned (but not on the first batch itself).

1.0.0 - 13/06/2014
  • Initial release. This is a replacement for V1 of this connector. This new version also uses v2.0 of the Facebook Graph API (https://developers.facebook.com/docs/apps/changelog).
  • NOTES (Relative to V1 of this Connector)
    • CHANGES
      • New FriendsWhoUseThisApp added.
      • New Messages table added to retrieve messages sent from user to a fan page.
      • New MyAccounts table added to retrieve token for new Messages table.
      • Added new Post table to retrieve the details for a particular post id.
      • Added has_comments, has_at_least_this_many_comments, has_likes and has_at_least_this_many_likes columns to the Statuses table.
      • Added comment_link and like_link columns to TaggedForItem table.
    • BREAKING CHANGES
      • The SearchForPosts table has been removed as Facebook have removed this feature from V2 of their API (https://developers.facebook.com/docs/apps/changelog).
      • The QuestionResponses and QuestionVotes tables have been removed as Facebook have removed this feature from V2 of their API (see https://developers.facebook.com/docs/apps/changelog and https://developers.facebook.com/docs/graph-api/reference/v2.0/question_option).
      • (POSSIBLE BREAKING CHANGE) The LikeCountForItem and CommentCountForItem table results are now cached. This should not be a breaking change if you are clearing the QVSource cache for this connector before doing a new reload.


(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