Trello and Qlik Sense Part 2

[Yianni_Ververis]

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:

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

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-i...

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

That's it!

Yianni