While ago I wrote a simple tutorial on how to connect to Trello API and get the lists and cards into Qlik SenseTrello and Qlik Sense.

 

Today I will go one step further and add more fields into Trello so we can import them into sense and use them as filters. I will use the labels and due dates.

 

I like to use labels to highlight the importance or urgency of each project. So I named all of the labels as:

2017-07-19 08_19_31-_ SaaS Demo on Qlik - Demos & Best Practices _ Trello.png

 

So in one of my cards, I have given it a label of normal and I have added a due date for today which I also marked it as complete. So, this is how my card should like

 

2017-08-18 16_26_22-_ Qlik Design Blog on Qlik - Demos & Best Practices _ Trello.png

 

Now lets go to my Qlik Sense and try to re import the cards.

 

I have changed the api path from last time to :

https://api.trello.com/1/boards/[board-id]/cards?fields=all&members=true&key=[key-id]&token=[token-id]

 

Here is my load script

 

LIB CONNECT TO 'Demos - Trello-cards';

RestConnectorMasterTable:
SQL SELECT 
"id" AS "id_u1",
"name" AS "name_u0",
"idList",
"url",
"due",
"dueComplete",
"__KEY_root",
(SELECT 
"id",
"idBoard",
"name",
"color",
"uses",
"__FK_labels"
FROM "labels" FK "__FK_labels"),
(SELECT 
"id" AS "id_u0",
"avatarHash",
"fullName",
"initials",
"username",
"__FK_members"
FROM "members" FK "__FK_members")
FROM JSON (wrap on) "root" PK "__KEY_root";

[labels]:
LOAD [id] AS [trello.label.id],
[idBoard] AS [trello.board.id],
[name] AS [trello.label.name],
[color] AS [trello.label.color],
[uses] AS [trello.label.uses],
[__FK_labels] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_labels]);

[members]:
LOAD [id_u0] AS [trello.member.id],
[avatarHash] AS [trello.member.avatarHash],
[fullName] AS [trello.member.fullName],
[initials] AS [trello.member.initials],
[username] AS [trello.member.username],
[__FK_members] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_members]);

[root]:
LOAD [id_u1] AS [trello.card.id],
[name_u0] AS [trello.card.name],
[idList] AS [trello.list.id],
[url] AS [trello.card.url],
//[due] AS [trello.card.due],
    Date([due],'MM/DD/YYYY') as [trello.card.due],
[dueComplete] AS [trello.card.dueComplete],
[__KEY_root] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

 

If everything works well you should be able to get all of your cards in the QVF. Now, lets add the filters for priority and completed

2017-08-18 16_43_49-.png

 

That's it!

 

Yianni