Are you a Trello user and want to start integrating your cards with Qlik Sense and getting some statistical analysis out of your projects' workflow? Here I will show you how to connect to you boards and get your lists and cards into Qlik Sense.

 

First, you need to have a developer’s key and an authorization token, both generated for you by trello.com.

  • To get you Key you need to go to https://trello.com/app-key
  • To get your Auth token, at the same page, right under your key, there is a link "Token". Click on that and it will take to a page that asks you "Let Server Token use your account?". You need to click "Allow" and then copy the key.

 

Then, lets go to Qlik Sense and setup our Rest Connector to get our data. All of the desired template links are in Trello Developers. Here I will just create a simple connection that retrieves all of the lists and their cards from a single board

  • Go to your "Data Load Editor"
  • Create a new section and name it Trello
  • Create a new connection using the "Qlik REST Connector"
  • In the url add "https://api.trello.com/1/boards/[board_id]/lists?cards=open&card_fields=name&fields=name&key=[key]&token=[token]". Don't forget to substitute the key and token that you got above. To get your board id, simply open your trello board and go to the url. It should be in the form of "https://trello.com/b/[board_id]/[your-board-name]".
  • Method is "GET"
  • Under Authentication select "BASIC" and add your username and password, give it a name and save it.
  • 2017-07-19 07_49_08-Qlik Sense Desktop.png
  • Click on "Select Data" and select what you want to get. If you select "root", you will get only the lists. If you select cards too, then you will get all of the lists with their cards
  • The REST Connector should generate the load script for you and should look something like this

 

LIB CONNECT TO 'Trello-board-list';

RestConnectorMasterTable:
SQL SELECT
"id" AS "id_u0",
"name" AS "name_u0",
"__KEY_root",
(SELECT
"id",
"name",
"__FK_cards"
FROM "cards" FK "__FK_cards")
FROM JSON (wrap on) "root" PK "__KEY_root";

[cards]:
LOAD [id] AS [card_id],
[name] AS [card_name],
[__FK_cards] AS [list_key]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_cards]);

[root]:
LOAD [id_u0] AS [list_id],
[name_u0] AS [list_name],
[__KEY_root] AS [list_key]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;






 

The end result should be something like :

2017-07-18 10_48_40-Qlik Sense Desktop.png

 

There are a lot of possibilities here. You can combine this with some power ups/custom fields that you would like to track and get more detail into your visualizations.

 

This is it!! Now with few steps you can add your lists and cards intro Qlik Sense and create charts or KPIs based on these data.

 

https://branch-blog.qlik.com/trello-and-qlik-sense-2da6e1912967

 

Yianni