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; 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.
Here is an example of how a JSON data response can look like:
"company": "Svebeck Consulting",
"streetAddress": "SomeStreet 12",
"postalCode": "110 10"
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.
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:
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:
FROM JSON (wrap off) "all" ArrayValueAlias "@Value";
LOAD [@Value] AS [@Value]
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.
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!
- Next >>