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

[mgranillo]

Yianni,

I have a request. Can you write a post about why we should use Angular and the Capabilities API?  I've enjoyed reading your posts like Creating a website with Angular and the Capabilities AP‌ and I'd like to trying using some of what you discuss but I don't understand a proper use case.  Qlik Sense's and/or Qlikview's out-of-the-box functionality can meet most of my my needs and much quicker than building a web app.  What are the benefits other than appearance?  How are your web apps better with Qlik?  How are your web apps better than a Qlik app?  Etc.  Sorry for my ignorance, I'm not a web developer, just interested.

Thanks,

Mike

[Yianni_Ververis]

Hello Mike,

There is no right case scenario for use of Qlik Sense. If you are not a developer and you are happy with just iframing a Qlik Sense Sheet, then this is the best approach. I usually use Angularjs to build large websites with various pages/sheets. I just have a better control of the display of the objects and more control of the user guided experience. I can see what the user is clicking, selecting and post it to Google Analytics so in our team we can analyze what it works and what not in a website that uses the Capabilities Api to display Qlik Sense objects.

The websites (mashups) we build are not better than Qlik Sense, they are from Qlik Sense but displayed in a different way, with more graphics, links, different navigation etc.

We are building these to show everyone the different capabilities of such a great product! You can do so many things from the APIs, like create new charts, apps, reload apps, create load scripts etc. The possibilities are too many to write about here.

Best,

Yianni

[mgranillo]

Yianni,

Thanks for that feedback.  You've given me somethings to think about.  Keep posting, I really enjoy the content you put out in the community.

Mike

[Anonymous]

Hi Yianni

Amazing article

I posted you a question in the previous part, but maybe it is better to post it here if this is the last part. I apologize if it is a mistake!

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

Kind regards!

[Yianni_Ververis]

Hello Pablo.

Is this a custom field? I do not see it in my cards. But n general, yes, you can extract any field that is in the card

[Anonymous]

Hello Yianni,

Thank you for your response. I understand it now: Estimated and Spent fields are part of the Plus For Trello plugin for Chrome. It is not possible to extract this data using the API

[Yianni_Ververis]

Hello Pablo,

You can extract any data from the card! add .json in the card url and you will see the actual fields and their values. Then you can extract from their api

[elenitof]

Hello jvs

I'm starting my learning in this fantastic tool that Qlik Sense and looking for way to connect in your TRELLO matter is very good ...
If you have more material like this please post
Thank you