Introduction

Most of you are probably aware and have already participated in the $100K competition, that Qlik has created for the College basketball tournament.

My colleague Arturo has a nice introduction on what the app is and how to get involved @ https://community.qlik.com/t5/Qlik-Design-Blog/Win-100K-The-Bracket-madness-has-started/ba-p/1558557

What very few are aware of, is how we designed the entire solution and why we picked each component.

The first and most important decision was about which Qlik product to use. We’ve decided on using Qlik Core, the Native cloud analytics development platform, because we needed to successfully handle thousands of concurrent users as fast as possible, in an independent cloud solution. The beauty of Qlik Core is that we take advantage of just the Qlik Associative Engine and we decide on the front end how to handle the data that comes from it. This makes the app ultra-fast since it is opening one WebSocket connection per user session and the data transferred are just simple json objects!

The second part we were worried about, was concurrency which leads to scalability. We needed to be able to handle thousands of concurrent users and if the server was reaching a certain percentage of Ram and Cpu, spin another one and route the new requests to the new server. That is where Qlik’s OSS awesome core-scaling repository came to be our key and the center of our solution.

The Core-scaling Solution

This Github repository was the beginning and the foundation of the entire solution. I used Kubernetes as our container orchestrator and started with the main containers that R&D has included in the public repo, Grafana, Prometheus, Custom Metrics Api, Nginx, Qix Session, Mira, License Service and the Qlik Engine. This repository is based on Google cloud, but I have made it work on Azure and AWS without any issues.

Now, each of these play a crucial role into the cluster and they are all needed.

Custom Metrics Api is an Api service that is used to send metrics from various Kubernetes services to Prometheus. It is mostly required on the scaling part of the engines which is based on the average sessions.

Prometheus is a service monitoring system that collects metrics from configured targets at given intervals. In our solution, we collect metrics from all of the servers so we can monitor performance and alert any issues, infrastructure or software related.

Grafana is a dashboard for visual representations of the metrics that we are collecting. In the repo you will find a custom json dashboard for the engines. You can view the resources utilized, total current sessions vs sessions per engine etc. I have included one more dashboard to monitor the health of the entire cluster, like the total Ram and Cpu used of all the services and totals for each one.

Nginx Ingress controller is a reverse proxy that handles all the security and traffic flow to the cluster. I will explain later a common user flow to the cluster.

Qix Session handles the placement based on custom metrics and set up of a session.

Mira is a Qlik Associative Engine discovery service for containerized environments. Mira finds the available Qlik Associative Engine instances and the properties of each instance.

License Service checks the license expiration or token consumption.

Qlik Associative Engine is the central service in the Qlik Core stack. It is designed specifically for interactive, free-form exploration and analysis. It fully combines large numbers of data sources and indexes them to find the possible associations, without leaving any data behind.

Once I had the initial repo, I created a cluster in Azure and I started deploying. You can do the same by following this guide to create the cluster https://community.qlik.com/t5/Qlik-Design-Blog/Azure-Kubernetes-Services-and-Qlik-Core/ba-p/1515128.

There are 2 ways of deploying to any kubernetes cluster, Microsoft, Amazon or Google. Either by following the instruction in core-scaling repository https://github.com/qlik-oss/core-scaling and using kubectl commands to deploy the yaml files, or by using helm charts which I have created in https://github.com/yianni-ververis/core-scaling-helm repository.

Customizing the Solution

OK, we have Qlik Core and the Grafana dashboard where we can view the user sessions and they are all deployed in the Kubernetes cluster but what about the rest of the application? We need to host the mashups, get statistical data for each basketball game, handle user authentications and store users, their profiles and their brackets.

The first part with the websites was the easiest one. We’ve just created one docker image for bracketchallenge and one for bracketguru. We bound the DNSs to the cluster and let Nginx Ingress controller route the traffic based on the domain. So, if you typed in the URL bracketchallenge.qlik.com you will be redirected internally to the qdt-bracketchallenge server and if you typed bracketguru.qlik.com, you will be redirected to the qdt-bracketguru server.

Now that we got the websites’ physical location out of the way, the next question is how are going to handle authentication, with focus on security and data integrity. We created a Nodejs API server that handles the user authentication, creates sessions, checks and writes into the database the user data like profile and the desired bracket. It is also used to handle the authentication for stats.com and feed the desired stats to Qlik Sense. Then, created another docker image and pulled it in our Kubernetes cluster.

Each server is sending data to the Custom Metrics Api server which then are stored in Prometheus. Then we use Grafana to view all the stats on the Qlik Associative Engine like how many current sessions we have open, what is the distribution on the running Associative Engines, how much memory and Cpu is consumed on each one etc. These sessions are critical on determining how many Associative Engines to run. Kubernetes has a controller called HPA (Horizontal Pod Autoscaler). This, based on some metrics, in our case the Sessions, decides when to spin another pod / engine. We set the Qlik Associative Engines not to accept more than 3000 sessions and when we are averaging 1750 sessions per engine, then we need the HPA to start another engine and start sending new sessions there. You need to fine tune those settings to match your qvfs and what works better with the Ram usage. You can max the HPA to how ever many pods your cluster can take. If you combine this with a Kubernetes Cluster Autoscaler, you can have infinite amount of sessions! The only limit would be of course your budget. :smileyvery-happy:

Having all of the puzzle pieces together, let’s see what a user flow will be like. First you will type the url to bracketchallenge.qlik.com. Nginx will route your request to the bracketchallenge server where it will send you back the authentication page. From there, login or create a user, the mashup will send, in both scenarios, your request to the Api Server, which will validate you against the database and issue a session token. Once the React webpage gets your session and validates it for its integrity, you are guided to the bracket page. There, a new request is sent to the cluster and more specifically to the Nginx Ingress controller where it will redirect it to the Qix Session server and initiate a Websocket connection with a new session. Once we have established a session, we go to Mira to see our available Associative Engines and place our connection to the most appropriate one. I need to make a side note here, that Mira sees and utilizes the availability of the Qlik Associative Engines, only ifs they have been cleared by the License Service and that the license provided in the cluster, has not expired or the number of tokens have not been completely consumed. Once the Websocket connection has established, enigma.js opens the app and gets a hypercube with the available teams.

Conclusion

Qlik as a leader in the BI world, has many products that are here to transform your business. Bracketchallenge.qlik.com is one example that utilizes the latest platform, Qlik Core. Qlik Core is taking the Qlik Associative Engine into your cloud and making it part of your business infrastructure. Even though this solution is on AWS, you can still use it on Microsoft Azure or Google Cloud. It does not have to be on Kubernetes either. Qlik OSS has examples for Docker swarm and Nomad https://core.qlik.com/tutorials/orchestration/

This journey was a tremendous experience for me as a developer and I gained a lot. I hope you enjoyed this article as much as I did and if you have any questions, feel free to drop me a note. If you have not done so, please join our community of developers in qlik-branch.slack.com and subscribe to the #qlik-core channel.

Yianni

**************

Editors Note: This blog originally posted via Qlik Community this past Friday, 4/5. But since the NCAA championship is tonight, we thought it deserved a refresh to our dev community who may have missed it. Thanks for reading + commenting + clapping!

Behind the scenes with bracketchallenge.qlik.com and Qlik Core was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.

Qdt-components is a library that we’ve created to support the Demo Team’s work. And it’s awesome…

ℹ️ In the past, I talked about how we can use it with various frameworks like simple Html, Angular 5 and React‌ as well as a Wordpress plugin.‌

ℹ️ I have also shared with you how some of the components with examples on how to use them with picasso.js.

Since these, we have added some more components! Below are the latest additions with examples on how to use them in your websites. Here are the latest additions with v1.3.5…

• Stacked Bar Chart

• How to Embed object from 2 different apps
• Table with the Visualization API
• Table with the Engine API
• Search with the results in the dropdown from the Engine API

• Custom Filters as a Dropdown, as a list object or as a horizontal tabular menu

• Vertical Gauge indicators with Picasso.js

• Range Area with Picasso.js

• Create your custom chart based on the already predefined components

Enjoy! And let me know if you have any questions in the comments.

Qdt-components — latest additions was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.

This Summer, Julie Whipple blogged about Qlik’s partnership with Team Rubicon. She talked described how we helped Team Rubicon by creating an amazing mashup which gives the world a view of the awesome job Team Rubicon, it’s partners, and countless volunteers are doing around the world. You can find Julie’s blog here: The Value of Data — Team Rubicon and Qlik | Qlik Blog

Even though the data is complex, using Qlik Sense to bring it all together in one view was like a walk in the park, too easy!

Once the data was uploaded into a Qlik Sense app, we setup the entire mashup using the qdt-react-template. We set up the pages and the routes and then used qdt-components to connect to the server and get the KPIs, Charts and Maps. In order to that, all we needed is the config to server https://github.com/qlik-demo-team/qdt-react-template/blob/master/src/components/QdtComponent.jsx and we were ready to go!

We are so thrilled to use our awesome products to make a difference. In general, developing is fun, really fun. When you add in the fact that what we develop is having a positive impact on the world, it makes us proud to be part of such a great company.

Check out the Team Rubicon — Open Initiative project: https://teamrubiconusa.org/open/

Here are some helpful links referenced above:
qdt-react-template: https://github.com/qlik-demo-team/qdt-react-template
Examples: https://webapps.qlik.com/qdt-components/react/index.html#/
qdt-components: https://github.com/qlik-demo-team/qdt-components

Best,

Yianni

Developing for Good — Team Rubicon and qdt-components was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.

This story details the charts that we have so far…

You can also interact with them live on our react template.

• You can embed any object from Qlik Sense and the Capability API
• You can create any custom session object by defining your own definition properties like dimension, measures, title, subtitle, color, order, etc.

• There is a custom selection toolbar that we use extensively in our mashups.

• A picasso.js horizontal bar that detects the height of the div. Once the bars exceed that, it adds a scrollbar. It also features multiple selections and tooltip.

• The same idea but with vertical bars.

• A new picasso.js pie chart with legend, multiple selections and tooltip.

• A new scotterplot with multiple selections, tooltip and legend.

• And now the latest addition, a scotterplot with custom images.

• A simple line chart.

• A combo chart

More coming soon with this amazing library! Stay tuned.

— Yianni

Qdt-components & picasso.js — More Charts! was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.

qdt-components (aka Qlik Demo Team Components) is a library of components that we have developed within the Demo Team. These have been used in most of our mashups found in webapps.qlik.com and demos.qlik.com.

It’s a collection of filters, tables, bar charts, selection toolbars and even simple getting/creating object methods from the Visualization API. You do not have to worry about connection strings, loading RequireJS, jQuery, etc.

Basically, with this library everything is taken care of for you! The library can be used in a simple html page by loading the qdt-components.js or in React and Angular2+ via npm.

Today, I will show you how to load a simple object and create a session bar chart, in simple html and Angular 5.

✨ Simple html

• In your html add…

<head>
   <script type="text/javascript" src="qdt-components.js"></script>
</head>
<body>
   <div id="qdt1"></div>
</body>

• In your Javascript code, add…

var qConfig = {
   "config": {
      "host": "your-sense-server.com",
      "secure": true,
      "port": 443,
      "prefix": "/",
      "appId": "133dab5d-8f56-4d40-b3e0-a6b401391bde"
   },
   "connections": {
      "vizApi": true,
      "engineApi": false
   }
}

var QdtComponent = new window.qdtComponents.default(qConfig.config, qConfig.connections);

var element = document.getElementById('qdt1');

QdtComponent.render('QdtViz', {id: 'a5e0f12c-38f5-4da9-8f3f-0e4566b28398', height:'300px'}, element);

More resources! Here’s a simple html template we have on GitHub and here’s a live demo of this in action.

✨ Angular 5

• Install Angular cli or get the prepared template
• Install the package…

npm install --save qdt-components

• Create new component by typing in the command line…

ng generate component components/qdt-components --style=less

• In your qdt-component.component.ts import the library and import or set the qConfig with the connection details…

import { Component, OnInit, ElementRef, Input } from '@angular/core';
// import * as qConfig from '../../../qConfig.json';
import QdtComponents from 'qdt-components';

let qConfig = {
   "config": {
      "host": "your-sense-server.com",
      "secure": true,
      "port": 443,
      "prefix": "/",
      "appId": "133dab5d-8f56-4d40-b3e0-a6b401391bde"
   },
   "connections": {
      "vizApi": true,
      "engineApi": false
   }
}

• Replace the class with…

export class QdtComponentComponent implements OnInit {

@Input() Component: Function;

@Input() props: object;

static QdtComponent = new QdtComponents(qConfig.config, qConfig.connections);

constructor(private elementRef: ElementRef) { }

ngOnInit() {

   QdtComponentComponent.QdtComponent.render('QdtViz', this.props, this.elementRef.nativeElement);

}
}

• After this, you can start using it in any other component. In the html, add…

<qdt-component [Component]="QdtViz" [props]="{id: 'a5e0f12c-38f5-4da9-8f3f-0e4566b28398', height:'300px'}"></qdt-component>

More resources! Here’s an Angular template we have on GitHub and here are some live demos…

• Live Demo Angular 5
• Live Demo Angular 6

You can also use qdt-components with React and Vue. Here’s a live demo using React 16.3 (Git Repo) and here’s a live demo using Vue 2.5 (Git Repo).

✨ Looking for more? See these other resources for working with qdt-components

• Qlik Branch
• GitHub
• Npm: qdt-components‌

Let me know if you have any questions in the comments! You can also reach out via Qlik Branch Slack (at)yianniververis.

— Yianni

qdt-components ➡️ A Components Library That Can Be Used in Simple Html, Angular 6, React and Vue was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.

Hopefully you all attended Qonnections 2018 and saw Jimmie on the bike!!!

Bike.qlik.com is the latest mashup built entirely with Qlik’s open source libraries, Qlik Core, Enigma.js, Picasso.js, Leonardo UI and the Demo Team’s Qdt-components.

Here is (briefly) how we did it…

• Sensors are sending data wirelessly to the Raspberry Pi
• Node.js reads and writes the csv
• Qlik Core in Raspberry Pi is reading the csv with a reload script and saves the data
• Enigma.js connects and gets the data from Qlik Core then Picasso.js displays that data in a graphical format
• Node.js uploads the qvds on bike.qlik.com
• Bike.qlik.com is a Docker that runs two containers in an AWS EC2 instance — one for the Qlik Core / Engine and another for an apache server
• The htdocs and the apps for the Qlik Core, are mounted outside of the two containers so there is no need to reload containers or recreate the Docker images, once the html code is changed or the qvf is updated
• Once uploaded, a Node.js restful API endpoint reloads the session app, reads the new data from the just uploaded qvds and saves the qvf with the new data
• React.js with Qdt-components is connecting to Qlik Core on the server and is getting the data
• Qdt-components is rendering 11 KPI components and one search components on the Rider field
• Picasso.js is used to read the data and displays a scatter plot and a line chart with tooltip and multiple selections

Expect more examples in the coming future with these combinations of our Open Source Software! In the meantime, explore the Qlik Core Beta getting started guide to learn the basic concepts.

— Yianni

Bike.qlik.com was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.

Last month I talked about our new QlikBotNode, setting it up and getting it ready for Telegram with this post on the Qlik Branch Blog and this one on Qlik Community.

Today, I will talk about the steps needed to make your bot available for Microsoft Skype as well!

With this, I’m assuming that you have already forked the code from GitHub and have already went through the previous tutorial and set up for the Telegram bot (see links above)….

Now, let’s get started! 🏁

The Installation

• First you need to “Register a Bot with Bot Service”. Login to the Azure Portal at http://portal.azure.com/
• Click the New button found on the upper left-hand corner of the Azure Portal, then select AI + Cognitive Services > Bot Channels Registration
• Click the Create button to start the creation process

• Enter a Bot Name as you want to appear in your contact list
• You can use the free Pricing Tier for testing
• For the Messaging Point add your full route like https://{your-server}/api/sense-bot/microsoft/
• You can skip the Application Insight for now
• Once registered, go to settings and get the AppId and the Password. You will need to add them in your environmental variables

• Click on Manage to get the password

• Put the AppId and Password in your Environmental variables
• For Windows, go to “Control Panel” -> “System” -> “Advanced System Settings” -> “Environment Variables”
• For Linux, from your directory (~), type “nano .bash_profile” or “vim .bash_profile” and enter the variables there like
• export SKYPE_BOT_ID_SCV=’’
• export SKYPE_BOT_PASSWORD_SVC=’’
• While still in settings, go to “channel” and add Skype

Thats it! You’re done! 🏁 And for more Details on Register a bot with Bot Service, go here.

Now, let’s talk about Usage

• Type help for all of the available commands

• Click on Salesforce to get the available commands for the Salesforce App and then click on the Dashboard to get a list of KPIs

• Click on CIO Dashboard and then on Management

• If you want to change your language click on Language and select one of the available ones. So far we have 3 — English, Spanish and Greek but please feel free to add as many as you want.

And that’s it for usage! 🏁

But one more pro tip…

Make sure you follow the list below. They’re phenomenal resources and we’re here to help.

• QlikBotNode on GitHub
• QlikBotNode/Skype.md at master · yianni-ververis/QlikBotNode · GitHub
• Qlik Branch (project details)
• Qlik Branch Slack Community
• Qlik Community QlikBot Group
• (@yianni_ververis) | Twitter

Best,

Yianni

QlikBotNode for Microsoft Skype was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.

Hello everyone! Let’s talk about QlikBots.

Today, Qlik is releasing two open source QlikBot projects on Qlik Branch and GitHub. 👍

• QlikBotNode: A web service built in Node.js, Express.js and MySql that can connect to messaging platforms like Telegram, Skype, Microsoft Teams, Cortana, Slack, Google Assistant and Amazon Alexa.
• QlikBotNet: Built in .NET/C# and provides access to Qlik Sense content. It can also be used with the Telegram messaging platform. More details on that one here.

And specifically, let’s talk about QlikBotNode.

Based on the popularity of Juan Gerardo’s QlikBot for Telegram written all in C#, I created this QlikBot in JavaScript with Node.js‌ and Enigma.js. So what’s the difference between this and QlikBotNet? Besides the obvious difference in programming languages, QlikBotNode is a little simpler to use. You just click buttons to get info whereas with QlikBotNet, users can type their own questions such as “Could you show me the cost by family?”

So, while QlikBotNet may be more powerful for analysis, QlikBotNode provides quick and easy access to KPIs.

Now, let’s create your own chatbot with QlikBotNode.

Below details how to get started creating your own QlikBotNode for Telegram on any operating system that has Node.js installed.

• The server will be running under https so if you do not have valid certificates for your domain (or localhost if you are testing it on your local computer), you can have Qlik Sense Enterprise take care of that for you. From QMC, go to start->Certificates and then export for localhost using the “Platform Independent PEM-format.”

• Copy these certificates and paste them into {QlikBotNode folder}/app/server/certs/localhost
• I have used three apps in this: 1. helpdesk, 2. Salesforce and 3. CIO dashboard. The qvf for helpdesk is bundled in your Qlik Sense Desktop while the Salesforce one you can get here and the CIO Dashboard one you can get here.
• Now get the code from the Qlik Branch project (also available here).
• From the command line, using your favorite tool (I am using Git Bash) type npm install in the root directory to get all of the required modules.
• Install MySql or Xampp / Wamp and connect to it. Xampp is bundled with phpmyadmin so you can use that. Then, create the database and the tables by running this sql query.

Now, all messaging platforms (including Telegram) require some tokens so your code can authenticate with the platform’s servers. Once we have these tokens, for security reasons, we need to set them as environment variables.

So, let’s create our QlikBot and get our token.

• Once you install Telegram Desktop, go to your “BotFather” contact and type “/newbot”. Everything in Telegram that starts with “/” is a command. So we typed the command to create a new QlikBot. This will give you an options menu to configure your bot.

• Go ahead and give your Bot a name, an icon and a description. If you click on “Back to Bot,” you will get a button with the “API Token”. Take that token and store it as Environment Variable.
• In windows you have to go to “Control Panel” -> “System” -> “Advanced System Settings” -> “Environment Variables” and set a new one as “TELEGRAM_BOT_TOKEN”, while in Linux, from your directory (~), type “nano .bash_profile” or “vim .bash_profile” and enter a new line with the token “export TELEGRAM_BOT_TOKEN=######”.
• Now you should be ready to run your code. From the command line (again, I’m using Git Bash), type “gulp”. If everything goes well and you do not have any errors, you should see a message “[nodemon] starting `node app/server/server.js`”. This means your server is up and running and communication via the token auth has been established!

Now, let’s explore QlikBotNode.

• Type help to view all of the available commands.

• Click on the Salesforce button or type /salesforce

• Here, there are some buttons to get KPIs and one that takes you to the mashup for validation. My recommendation? Try them all!
• Click on the CIO button or type /cio and then click on "Management" to get the set of KPIs.

• Click on the Helpdesk button or type /helpdesk

That’s it!

Now you can a have a Node.js server running with your QlikBotNode and you can start experimenting with KPI commands in the Telegram app.

What Next?

As mentioned, the code for QlikBotNode is open sourced on Qlik Branch and GitHub (same with QlikBotNet — more details here).We encourage you to fork the projects on GitHub, add new features, test them and submit pull requests. We just ask that you please follow our open source guidelines found here.

Also, if you have any questions, don’t hesitate to ask in the comments below or join the Qlik Branch Slack. We have an entire channel that’s devoted to all that is QlikBot.

QlikBotNode with Node.js and Enigma.js was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.

You can create a mashup from within Wordpress, using the Capabilities API, without writing a single line of code.

And it’s easy — just add the settings with the host and the App ID, then the shortcode for the objects that you want and you are done.

But here are the instructions step-by-step…

• First go to your wp admin panel, under “Plugins” click on “Add New” and then search for “Qlik”

• Click on “Install Now” and then on “Activate”
• This will create a “Qlik Sense” settings page, just open that

• Here you need to define your host, the Virtual Proxy (prefix) and the App Id, as you would in a regular mashup
• Save changes.
• Then add the shortcode into your posts “[qlik-sense-object qvid=”ZwjJQq” height=”400" nointeraction=”true”]”- qvid: Is the object id as found in the “dev-hub/single-configurator”- height: The height of the visualization in pixels- nointeraction: Add this if you want to disable interactions. If you want the objects to have interaction, you can just omit this.

• I have added few objects with a specific height in a bootstrap template for better layout. Now, lets preview the page.

• Now just make sure you whitelist your url in the virtual proxy.

What do you think?

If you use it and like it, please give it a 5️⃣ star review in Wordpress.

Other Resources

• GitHub — yianni-ververis/qlik-sense-wordpress-plugin: A Wordpress plugin to create a Qlik Sense Mashup
• Qlik Branch

This is it!

— Yianni

476 Views Tags: none (add) wordpress, demo_and_best_practices, qlik sense, capabilities api

Originally published at community.qlik.com on November 10, 2017.

Qlik Sense Wordpress Plugin was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.

First, you need to have a developer’s key and an authorization token, both generated for you by trello.com.

• To get you Key you need to go to https://trello.com/app-key
• To get your Auth token, at the same page, right under your key, there is a link “Token”. Click on that and it will take to a page that asks you “Let Server Token use your account?”. You need to click “Allow” and then copy the key.

Then, lets go to Qlik Sense and setup our Rest Connector to get our data. All of the desired template links are in Trello Developers.‌ Here I will just create a simple connection that retrieves all of the lists and their cards from a single board

• Go to your “Data Load Editor”
• Create a new section and name it Trello
• Create a new connection using the “Qlik REST Connector”
• In the url add “https://api.trello.com/1/boards/[board_id]/lists?cards=open&card_fields=name&fields=name&key=[key]&token=[token]". Don’t forget to substitute the key and token that you got above. To get your board id, simply open your Trello board and go to the url. It should be in the form of “https://trello.com/b/[board_id]/[your-board-name]".
• Method is “GET”
• Under Authentication select “BASIC” and add your username and password, give it a name and save it.

• Click on “Select Data” and select what you want to get. If you select “root”, you will get only the lists. If you select cards too, then you will get all of the lists with their cards.
• The REST Connector should generate the load script for you and should look something like this…

LIB CONNECT TO 'Trello-board-list';

RestConnectorMasterTable:

SQL SELECT

"id" AS "id_u0",

"name" AS "name_u0",

"__KEY_root",

(SELECT

"id",

"name",

"__FK_cards"

FROM "cards" FK "__FK_cards")

FROM JSON (wrap on) "root" PK "__KEY_root";

[cards]:

LOAD [id] AS [card_id],

[name] AS [card_name],

[__FK_cards] AS [list_key]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_cards]);

[root]:

LOAD [id_u0] AS [list_id],

[name_u0] AS [list_name],

[__KEY_root] AS [list_key]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

The end result should be something like:

There are a lot of possibilities here. You can combine this with some power ups/custom fields that you would like to track and get more detail into your visualizations.

This is it!! Now with few steps you can add your lists and cards intro Qlik Sense and create charts or KPIs. Share your experience in the comments below.

Relevant Links: https://community.qlik.com/blogs/qlikviewdesignblog/2017/07/21/trello-and-qlik-sense

— Yianni

Trello and Qlik Sense was originally published in Qlik Branch on Medium, where people are continuing the conversation by highlighting and responding to this story.