Qlik Design Blog

August 2017 Previous month Next month

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 :



Here is my load script


LIB CONNECT TO 'Demos - Trello-cards';

"id" AS "id_u1",
"name" AS "name_u0",
FROM "labels" FK "__FK_labels"),
"id" AS "id_u0",
FROM "members" FK "__FK_members")
FROM JSON (wrap on) "root" PK "__KEY_root";

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]);

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]);

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!



einsteinshow.jpgIn this week's Qlik Design Blog I am pleased to share some tips from our own Mitul Vadgama of our Qlik Consulting Services team.  Mitul was recently working at a customer site when he noticed something that seemed a bit off when analyzing the customer's data. On the surface after loading the data everything appeared fine - but once they started performing selections, Mitul was able to identify the specific cause with the help of Qlik's Associative Difference, the elusive <NULL> value.


The Problem


The customer's data model had about 300+ fields with a number of these fields being of a nominal data type (also known as categorical data). Put simply, I like to describe nominal data as the attributes in the data that don't carry a numerical value. Examples of nominal data are as follows:





Mitul found that when business users were selecting values in a nominal field it gave the correct result, however when they performed advanced selections such as using the the Select excluded option available in a select list, the results did not match up against what was expected. Thanks to Qlik's Associative Difference they were able to quickly and easily identify that <NULL> values were being excluded when the customer performed a Select Excluded selection. Yes, I know that sounds weird, the Select excluded option excluded <nulls> from the exclusion selection. (read-on and watch the quick video so this makes more sense to you )


Click to enlarge image


The Fix


One way is to use the NullValue variable and NullAsValue statement set in the Data Load Editor settings.  (Qlik Help)



Set NullValue='<NULL>';

NullAsValue Field1, Field2;


In our example data we have <null> values in the StateProvince and OfficeStateProvince fields both in the Customers, and Employees tables. In the image above you can see that the <null> value is also not select-able in the filter pane.




Set NullValue='<Unknown>';
NullAsValue StateProvince, OfficeStateProvince;


After adding these settings to the Data Load Editor we were able to get the correct results as the <null> values were now being taken into consideration.


Click to enlarge image


Companion Video: Replacing Null Values in Multiple Fields


NOTE: To increase resolution or size of the video, select the YouTube logo at the bottom right of the player. You will be brought directly to YouTube where you can increase the resolution and size of the player window. Look for the 'settings' gears icon in the lower right of the player once at YouTube.


We hope this post has shown you another efficient way to handle <null> values in your Qlik application.


I'd like to thank Mitul for his valuable contribution to the Qlik Design Blog.






About Mitul: Mitul is a member of our Qlik Consulting Services team in the UK. He has worked on a variety of consulting projects using both Qlik Sense and QlikView with many enterprise customers. His passion is transforming data into valuable business insights, knowledge sharing, and enabling customers to get the most value out of Qlik products.





Can't see the video?


Download the .mp4 and watch from your computer or mobile device.

Continuing with the description of the new charts available in the Qlik Sense June 2017 release, today is the Box plot’s turn.


The American mathematician John W. Tukey introduced the box-and-whisker plot (called simply a box plot) in his 1977 book, "Exploratory Data Analysis".


Like the Distribution plot, the Box plot is a histogram-like method of displaying data and is appropriate to represent the degree of dispersion, skewness, and whether there are potential unusual observations in the data set. It’s particularly useful for comparing distributions between several sets of data, short of having several histograms close to each other so you can compare across them. The data center, spread and overall range are immediately apparent for each data set.

In my previous post about the distribution plot, I used an example data set containing data for 3 salespersons recording their monthly sales data. I'll reuse that data to illustrate how the box plot works.


A Box plot will typically help us to visualize 5 numbers, the statistical median represented as a horizontal line inside the box, the box ends show the first and third quartiles values. The whiskers indicate the range of the data and they are represented as horizontal lines ending in a small vertical line. Whiskers extend to the farthest points that are not outliers. Depending on the box plot configuration you choose, a provision is made for the representation of extreme values, typically upper/bottom quartile range +/- 1 x interquartile range or IQR. Additionally, outliers or extreme values are represented with dots.


Some general observations about our salespersons’ box plots:

  • The box plot is comparatively short – see Sheri. This suggests that overall monthly sales have a high level of similarity with each other.
  • The box plot is comparatively tall – see Dani. This indicates Dani’s monthly sales are quite different across the year.
  • The box plot is skewed – see Dani. There are more data points on the left/bottom (toward lower values), most of Dani's sales are small amount of sales.

To get the chart working in your Qlik Sense app it only requires one dimension (add a second dimension to compare across it) and one expression. Qlik Sense's new Box plot offers three standard presets, standard (Tukey), percentile-based, and standard deviation. For those of you with special needs or for those interested in learning more about fine tuning your chart, there’s a manual mode that give us full control over each one of the chart elements.


Enjoy it.

Arturo (@arturoqv)

In the this video edition of the Qlik Design Blog, I show you how I use the Web File connector to combine retro-video game pricing data (Sega Master System) with my own local video game collection. The Web File connector is available in all Qlik Sense products, including the free version of Qlik Sense Cloud (Basic). It is commonly known that the Web File connector is fairly simple to use, however I wanted to demonstrate its use in a fun and meaningful way as well as provide additional information that will help you understand how and when it can be used and what to look out for. Enjoy!


8-7-2017 2-34-02 PM.png

Sega Master System Value Analysis - Mike's Collection - Qlik Sense App


The video is 16 minutes long. I first show you how to simply use the Web File connector with a internet based source via HTTP:// (Current Master System Video Game Prices - GameValueNow.com) and then build a few Qlik Sense KPIs. Then I expand on possible issues you COULD run into with Web File, as well as an example using FTP://, then I conclude with a simple demonstration analyzing some of my video game value data. Analysis result: Purchasing a number of the cartridge titles separately from the cases and manuals and the combining to re-sell may prove profitable. Enjoy!


Michael Tarallo (@mtarallo) | Twitter




Qlik Sense Cloud - Using Web File


Time Index:

• 0:00 - 8:30 - Intro and using the Web File connector

• 8:30 - 10:38 - Additional information to be aware of: https://youtu.be/HEcYYn1hrrk?t=8m30s

• 10:38 - 11:40 - Using FTP with inline credentials: https://youtu.be/HEcYYn1hrrk?t=10m41s

• 11:40 - 12:46 - REST connector information: https://youtu.be/HEcYYn1hrrk?t=11m40s

• 12:46 - End - Fun information / demo using finalized Video Game pricing analysis app: https://youtu.be/HEcYYn1hrrk?t=12m52s


NOTE: To increase resolution or size of the video, select the YouTube logo at the bottom right of the player. You will be brought directly to YouTube where you can increase the resolution and size of the player window. Look for the 'settings' gears icon in the lower right of the player once at YouTube.


Sample Qlik Sense app and data attached in case you wanted to play.
Web File Help document: Loading data from files ‒ Qlik Sense


Can't see the video?


Download the .mp4 and watch from your computer or mobile device.

I've seen a few people asking recently if and how they can use webpack to build their mashup. The answer is yes, and I'm gonna discuss a few implementation details and provide some example code.


So to use the Qlik Capability APIs, you're probably already aware that you need to load the Qlik Capability API code, which includes loading a custom require.js file, and then requiring the qlik.js file through the loaded instance of require.js. The thing is, there's really no way to get around this, that's how the Qlik Capability APIs are loaded.


But you can still use webpack for all of your own project code, you just have to decide how you are going to load the custom require.js file and where you'll use the require.js instance to require qlik.js. This is how I've been doing it.


First, I load the Qlik custom require.js file in a script tag in the head of the document. Can you get fancier if you'd like with something like the script-loader, yea sure you can, but the goal here is to load that Qlik custom require.js file in a global context, and to me the simplest way to do it is to just include it in a script tag in the head of my html document.


Then, you'll need to set the require.js config and require the qlik.js file through require.js somehow. The trick here is that the require.js instance can be accessed with window.require. Also, since requiring files with require.js is asynchronous, and you'll almost certainly want to return some stuff when qlik.js is done loading, it's useful to use a promise here. This is what my module looks like for this in ES2015 -




You'll notice the config object which you should be used to for mashups, then how I'm using window.require.config to set the require.js config. Also, I explicitly set the path for 'qlik' because I find this helps avoid some errors, especially with regards to loading extensions. Then, I export a promise which resolves with the app from the openApp() method as the value. You can resolve this promise with the 'qlik' object, or multiple apps, or whatever your needs are, but for myself, most of the time, I'm just opening 1 app and I just resolve the app.


So in summary, if you want to use webpack with the Capability APIs then the Qlik custom require.js file will need to be loaded in a global context in some way, and then you'll be able to access the require.js instance on 'window.require' (but not just simply 'require' since webpack will use that keyword).

Filter Blog

By date:
By tag: