Qlik Community

Qlik Design Blog

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

Employee
Employee

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

2017-08-18 16_43_49-.png

That's it!

Yianni

8 Comments
Specialist
Specialist

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

0 Likes
776 Views
Employee
Employee

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

0 Likes
776 Views
Specialist
Specialist

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

0 Likes
776 Views
Contributor
Contributor

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!

0 Likes
776 Views
Employee
Employee

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

0 Likes
776 Views
Contributor
Contributor

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

0 Likes
776 Views