Generic Database in QlikView

Introduction

In QlikView, a generic database is a table in which the field names are stored as field values in a column, whereas field values are stored in the second column. A generic database can be used for attributes of various objects.

Structure of generic database

Example

In this example, a generic database containing two objects and attributes, like color height, weight and diameter, are common depending on objects and their values.

When we solve this problem with a QlikView generic database, the data is stored in a compact way. When working with this table using a QlikView generic database QlikView creates some internal tables automatically. When we load this table in a QlikView edit script we get multiple list boxes in sheet properties after reloading this process. QlikView generates one field for each unique value of the second column.

Syntax of generic database

Generic ( load statement | Select statement )

For a generic database, we use either a load statement or select statement, it depends on your choice.

Example

For data files: use load statement as in the following:
Generic load * from abc.xls ;

For database table: use load statement as in the following:
Generic select * from table name ;

So let's start with the creation of a generic database.

Step 1: Open the QlikView application

In the first step you need to open the QlikView application then go to File-> New then this window will be opened.

After clicking New option

Step 2: Open Edit Script

The second step is to open the edit script window from File-> Edit Script.

window of edit script

Then this window will be opened.

Step 3: Select Database

The third step is to select the database from the data tab and click on the Connect Button.

Step 4: Data link property window

In this window we select SQL Server and click on the Next Button.

Step 5: Connection tab

In the connection tab enter the server name and user name and password and select the database from your server and click on Test Connection.

Then you find that the test connection succeeded and click on the OK button.
Then click on the OK button.

Step 6: SQL Server login

Then the SQL Server login is required so enter a valid login id and password and click on the OK button.

Then this window will be opened.

Step 7: Select database

Now the next step is to select the database from the table.

select statement

Then this box will be opened and select the table from your database.

Step 8: Code for generic database

Now in the edit script, write this code for the generic database and after this reload it.

reload

Source code of generic database

Generic LOAD Id,
Name,
Post,
City;
SQL SELECT *
FROM Employee.dbo.Employee;

Step 9: Save QlikView file

After reloading the edit script, the next step is to save the QlikView file and click on the Save button.

save file

Step 10: Script execution progress

Then the script execution progress window will be opened and requires a login Id and password and click on the OK button.

Step 11: Sheet property Window

After this process, the sheet property window will be opened. Now here you can see QlikView generates some internal table automatically and click on the OK button.

main sheet

Now you see that the highlighted fields are the generic database that was created by QlikView automatically.

Step 12: Select table box

In the menu bar there is a layout option. Select Layout -> New sheet object -> Table Box.

Step 13: New table box

In this window we select all fields for showing the full row data and click on the OK button.

Now you can see this window, it shows the generic database table.

final window

Summary

This article is a summarized introduction to generic databases in QlikView applications and also explains how to use a generic database in a QlikView application.