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 :
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