Qlik Sense Feedback Form - Part 3 of 3

Getting Data from Airtbale with the REST connector.

I have divided Part 3 in 3 steps.

  • Step 1 - REST, and Qlik Sense REST connector
  • Step 2  - Airtable API
  • Step 3 - Loading the data from Airtable into Qlik Sense

[Everything in Part 3 would also be possible to do in QLIKVIEW. The REST connector works in the same way there.]

Step 1 - REST, and Qlik Sense REST connector

First, a disclaimer. I actually know very little about the REST connector, and the things I am going to write about. Before digging into this project of Airtable and Qlik Sense I had never before worked with the REST concept at all, so what I know now, is plainly a result of some weeks of trial and error. I also promised in the part 1 of this blog posts, that no coding was going to be needed. and that the solution can be done without any previous knowledge (except some Qlik Sense skills). I will stick to that and not dig deep into any code here. But the potential of this project is great, and if you add some basic JavaScript skills to it, the possibilities are massive. But that will be another blog post...

First; What is REST?

The 4 letters are coming from the words "REpresentational State Transfer", and is also sometimes called "RESTful".

A RESTful web service is providing a way to transfer data between computer systems on the internet. The data from a RESTful web service is normally coming in one of 4 ways:

  • As HTML
  • As XML
  • As JSON
  • As plain text

Of those formats, JSON is perhaps for non-developers the one which is most unfamiliar.

The 4 letters of JSON comes from JavaScript Object Notation. It is a format that is slowly replacing XML, for many reasons, but one of them is that it is more "readable" for humans but at the same time very structured and systematic.

Here is an example of how a JSON data response can look like:

{
  "firstName": "Robert",
  "company": "Svebeck Consulting",
  "active": true,
  "address": {
    "streetAddress": "SomeStreet 12",
    "city": "Stockholm",
    "postalCode": "110 10"
  }
}

It's quite readable in this simple form, right? And it is looking a lot like JavaScript in its "grammar", for those of you have seen that before.

REST also works the other way around and can be used to push data to a web service, this blog is not covering this at all.

RESTful API's are getting more and more common, and Airtable and Qlik Sense is supporting it  - so that's a perfect match!

The rest connector is installed together with Qlik Sense Desktop - and it's license free. It is also very soon available in Sense Cloud Business.

Have a quick look at this (rather old) but still ok video on how the Sense REST connector works in Qlik Sense.

Here is how I setup the rest connector in Qlik Sense Desktop:

1. Open your Qlik Sense Desktop and open an app, or create a new empty app.

2. Go to the Data Load Editor and push the button "Create new connection"

3. Select Qlik REST connector

You will now see this window:

You see that you are supposed to have a URL for the request and also define a "Method". In our Airtable project, we will only use the method "GET", since we will only Get data from our Airtable database. But what do we write in the URL?

I hope you know what a URL is.... if you do not - we can keep it simple and just say it is a internet address, just like http://google.com 

We will now do a simple example on how to use the REST connector.

I found a funny site that give us a great chance to learn something. Lets continue!

4. Just for a start, enter this URL string into the REST connector URL path:

http://www.anagramica.com/all/:banana 

5. Leave everything else just as it is, and press "Test Connection"

You should normally now get a notification saying: "Test Connection. Test was successful."

If you do not, double check that the URL you typed was exactly like I wrote above. If it still does not work - well then I can't help you more. You might not be fully connected to the internet...or behind a firewall, or you may just not have a lucky day.

6. Hoping you hade the "Test Successful" response, we name the connection "Anagramica" and press Create.

You should now have this:

 

7. Now press the "Select Data" icon (The middle one).

You should get this screen:

 

Lets keep things simple, and make no changes. I encourage you to get back to this screen and play around with different settings. Worst case, you might learn something. :-)

8. Just tick the box on the left of "all". This will make the "Insert script" button on the lower right corned enabled.

9. Press the "Insert script" button

You should now have this script:

RestConnectorMasterTable:
SQL SELECT
 "@Value"
FROM JSON (wrap off) "all" ArrayValueAlias "@Value";

[all]:
LOAD [@Value] AS [@Value]
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

What does it say?

I assume you are familiar with the Qlik Script language, so you will already have a good guess.

Basically - We are selecting a field called @Value from the URL we entered and we load it into a table called [all].

10. Save your app and run the script (Load Data) to see what is happening!

Go to you app overview, create a sheet or open a existing sheet, go into Edit mode and add a filter pane with the dimension @Value.

You should get this result:

What is this result? Well, the URL we called is a site that is creating anagrams. Look again at the URL.

http://www.anagramica.com/all/:banana 

What we got from this RESTful Web Service is different (good ones and bad ones) anagrams of the word "banana"

You can off course change the word banana to something else, like apple (creative thinking, right?)!  This API is limited in number of characters and the word must only contain normal characters, no spaces etc. But it's fun and a good example that is simple for a first try.

If you want to change the word, you must go back to the Data Load Editor, and edit the connection we named "Anagramica". Just replace the word banana in the end of the URL to any word of your preference. Test the connection and if it works, reload the data.

Now you are skilled enough on the Qlik Sense REST connector for the next step!

Lets go to the Airtable database and find out how to get the URL to your database we created in Step 2!