In this blog, I will give you some best practices and tricks when you are loading table to generate your data. But before, I will review the different ways to load data in a QlikView or QlikSense report.

1.How to retrieve data in QlikView

You have 2 different possibilities to load your data in your report:

  • Database connector
  • File connector

Picture1.png

a) Database connector:

If your data are located in a database, you must use this kind of connectors.
To connect to a database:
Open “Script Editor”

Picture2.png

Click on “Tab” and “Add Tab…”

Picture3.png

Give a name to the new Tab and clock “OK”

Picture4.png

Select the data source

Picture5.png

Select your connection (for OLE DB, ODBC, connections should be created in the ODBC administrator tool from windows)
Remark: You can use a special connection on the ETL tool from QlikView named QlikView Expressor

Picture6.png

For this example, I want to connect on a Oracle database:
Select the connection and click “OK”

Picture7.png

Select a table (1), than select the fields you want see in your report (2) and click “OK” (3)

Picture8.png

TRICK 1: If you use the “Connect” option and you add the User ID and the password in the connection interface, they will be put in the SQL script in an encrypted format

Picture28.png

Picture9.png

 b) Data file:

You have 4 different options:

Picture11.png

(1) Table Files: you can select the following kind of files

Picture12.png

(2) QlikView File: You can load .qvw file (QlikView file)
(3) Web File: You can load a file coming from a website
(4) Field data: you can load specific rows coming from a field located in a database

In this example, we select a .qvd file using the 1 option (table file)

Picture13.png

You have 2 options:
Click “Next” and “Next”: you access to the column choose interface
Remark: To remove a column, click on the cross. Then click “Next ”

Picture14.png

Check the SQL. If it’s ok, click on “Finish”

Picture15.png

Click on “Reload” to load the data

Picture16.png

Best Practices: create a variable path
If you must load data coming from files located in a specific repository, and if this repository is going to change after the report is published in different environments, it is recommended to create a variable to define the folder path.
Go on the Main Tab and create the variable “FilePath”. Don’t forget the “;” at the end

Picture17.png

On the other tab where you load data coming from file located in the same folder, add the variable before the name of the file.

Picture18.png

After the deployment on other environment, you just have to update the variable and of course, reload the data.

2.Optimize the data recovery in QlikView / QlickSence

In this example, some tables are loaded just for one field. We can optimize this schema with using a mapping function. The goal is to limit the number of tables used directly in the schema.

Picture19.png

Warning: The mapping function can only be used to add one field in a table.

In our example, we want to add the filed “Product Group desc” in the table “ITEM_MATSER”.
To create a mapping tab:
Add a new Tab just after the main tab

Picture20.png

Use the function “MAPPING” in the script as follow:

Picture21.png

In the destination table, add the field with the following function “Applymap” as follow:

Picture22.png

(1) Put the name of the Mapping table you have created
(2) Put the name of the key field
(3) Put the name of the field you want to show in your table

Don’t forget to comment the script from your mapped table.
After refreshing the data, you will see that the table has disappeared and the filed has been added in the main table.

Picture23.png

3.How to add a Master Calendar table

You can generate automatically a master calendar if you need to have all the days located in a period. This calendar will be generated in 3 different steps:

a) Creation of the Min / Max date temporary table.

Create a new tab and add the following script:

Picture24.png

 b) Creation date temporary table.

Add the following script to create a temporary table with all the dates between the MIN and MAX date you have define using the function “AUTOGENERATE”

Picture25.png

Note that we drop the result from the MIN / MAX table at the end of the creation from the temporary table.

c) Creation Master Calendar table.

After the generation from the temporary table, add the following script to create all the different date fields you need (Year, month, week …)

Picture26.png

Remark: to join your tables, you must give to your new generated field the same name than the field you have used to create your calendar. The result should be like this.

Picture27.png

I hope that these best pratices and tricks will help you !