Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Trello and Qlik Sense

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]&t...". 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

7 Comments
Luminary
Luminary

Brilli!

0 Likes
850 Views
Luminary
Luminary

Nice Yianni.

0 Likes
850 Views
tedemang
New Contributor III

Very neat!

0 Likes
850 Views
marmentrout5
Contributor

Very nice!  I can see some potential uses for this.  Thanks for sharing.

0 Likes
850 Views
santiago_respane
Valued Contributor

great!!! Thanks for sharing!

Kind regards,

0 Likes
850 Views
pabloalfonsogar
New Contributor

Amazing! Is there any way to extract the "spent" field from a card?

Kind regards!

0 Likes
850 Views
Rodo
New Contributor

Hi Yianni,

 

Thanks for sharing!!

It`s really good stuff!!

I wonder if you can help me... I am having a trouble getting the actions, I only can get 50.

I have tried with several ways and have the same result:

https://api.trello.com/1/boards/YUIhPDaX/?fields=id&actions=createCard&actions_limit=1000&action_fie...

https://api.trello.com/1/boards/YUIhPDaX/actions?limit=1000&key=xxxx&token=xxxx

https://api.trello.com/1/boards/YUIhPDaX/actions/limit=1000&key=xxxx&token=xxxx

and every posible combination...

One thing I note is that setting the limit parameter in a value below 50 works ok, but whit values over 50 always brings 50.

Thanks again!!

Regards. 

 

 

731 Views