Book Image

Qlik Sense Cookbook - Second Edition

By : Pablo Labbe, Philip Hand, Neeraj Kharpate
Book Image

Qlik Sense Cookbook - Second Edition

By: Pablo Labbe, Philip Hand, Neeraj Kharpate

Overview of this book

Qlik Sense allows you to explore simple and complex data to reveal hidden insight and data relationships that help you make quality decisions for overall productivity. An expert Qlik Sense user can use its features for business intelligence in an enterprise environment effectively. Qlik Sense Cookbook is an excellent guide for all aspiring Qlik Sense developers and will empower you to create featured desktop applications to obtain daily insights at work. This book takes you through the basics and advanced functions of Qlik Sense February 2018 release. You’ll start with a quick refresher on obtaining data from data files and databases, and move on to some more refined features including visualization, and scripting, as well as managing apps and user interfaces. You will then understand how to work with advanced functions like set analysis and set expressions. As you make your way through this book, you will uncover newly added features in Qlik Sense such as new visualizations, label expressions and colors for dimension and measures. By the end of this book, you will have explored various visualization extensions to create your own interactive dashboard with the required tips and tricks. This will help you overcome challenging situations while developing your applications in Qlik Sense.
Table of Contents (16 chapters)
Title Page
Copyright and Credits
Packt Upsell

Extracting data from the FTP server

In the last recipe, you learned how to fetch data from a website using the web file connection. With the same connection type, we can extract data from files available in an FTP server.

To demonstrate this recipe, I'm using a local FTP server with user authentication. My server is localhost, my userid is qlik, and my password is pwd.

As a general rule, you must use the following parameters in the URL if the server requires authentication to retrieve data from a file ftp://<userid>:<password>@<servername>/path/filename.qvd.ftp://%3Cuserid%3E:%3Cpassword%3E@%3Cservername%3E/path/filename.qvd

Getting ready

When extracting the data from the FTP server:

  1. Open your web browser to check whether the URL is correct and whether you have access to the server (I prefer Firefox for this task).
  2. Insert the following URL from which you can see a list of folders and files from a local FTP server: ftp://qlik:pwd@localhost(change the URL with your credentials and server name):
  1. Navigate to the folder with the files you need:
  1. Right-click on the file and select Copy Link Location:

In my example, I chose ROUTE_BUS.csv:

  1. Open an existing Qlik Sense application or create a new one.
  2. Open theData load editor.
  3. Click on Create new connection and select Webfile.
  4. The Select web file window will open.
  5. Insert the URL copied from the web page to set a connection with the file to extract data: ftp://qlik:pwd@localhost/chapter%201/QlikSenseData/ROUTE_BUS.csv.
  6. Name the connection as QlikSense Cookbook FTP ROUTE_BUS, as shown in the following screenshot:


How to do it…

  1. In the list under Data connections, select QlikSense CookbookFTP ROUTE_BUS and click on Select data. This will open up a preview window listing the contents of the file.
  2. If the file is large, more than one megabyte, the preview can take a long time to open because of low internet speed and bandwidth when connecting to the remote FTP server.
  3. Change the value under Field names to Embedded field names.
  1. The preview of the table will look like the following screenshot:
  1. Select all the fields from the table in the preview window. Click on Insert script to load the web data in the application.
  2. Name the table as ROUTE_BUS and the script will read as follows:
FROM [lib://QlikSense Cookbook FTP ROUTE_BUS] 
(txt, codepage is 28592, embedded labels, delimiter is ',', msq); 
  1. Save and load the data. Once the script is successfully loaded, the Data model viewer will show the loaded table.

How it works…

Qlik Sense connects to the FTP source file using the stored data connection. Once connected, it identifies the content of the source folders and lists them in the preview window.

If the file is large, the preview will take a long time to open.

For each file, you must create an FTP connection, so this recipe is used in very specific use cases.


If you need to read several files, files that are large in size or both,  I recommend using an FTP client to download the files beforehand to a local folder.

You can perform the FTP download using the Windows FTP command. The LOAD script can trigger the FTP command using EXECUTE if legacy mode is enabled.

There's more…

Qlik Sense can also extract data from other data formats, such as XML, XLSX, XLS, or QVDS. The underlying principles remain the same as explained in the preceding recipes.

See also…

  • The Creating a master library from the Data model viewer recipe
  • The Activating the legacy mode in Qlik Sense® desktop recipe