Connecting to database

QlikView is capable to connect to multiple databases via ODBC and OLEDB connectors. It also supports Salesforce connector.

In order to connect to any of the databases we need to have the respective database’s driver installed in our machine(By default in some scenarios, the drivers gets automatically installed in your machine when you install the database). Qlikview offers a very simple method to connect to database. Simply goto Edit Script window by pressing Ctrl+E or clicking on File->Edit Script.

In Edit Script window, you will find the following screen:

Now you may be wondering what are all these statements which already exist in your scripting window though you haven’t typed anything yet. So these are pretty straightforward statements which are present by default in the scripting window. These statements basically show the formats for various entities like date, timestamp and many more. For eg, if you look at the statement

SET DateFormat='DD/MM/YYYY';

You will find that date format has been set to DD/MM/YYYY which means that all the date fields in your application will have the same format. If in case you need to change the format for all the dates in your application as DD-MM-YYYY, you simply need to change this statement as

SET DateFormat='DD-MM-YYYY';

And your dates in the application will appear in the format DD-MM-YYYY. Exceptionally if you explicitly need to change the format of certain dates to YYYY-MM-DD then you can use the Date() function which we will discuss in later chapters.

Connecting to database:

In order to connect to any database, you will find an option at the lower panel of the edit script.

It gives you 2 options to connect: ODBC and OLEDB. In order to connect through ODBC you need to create a DSN wherein you will specify all the details for the database you want to connect. If you don’t know how to create a DSN you can refer the following link: https://support.microsoft.com/en-us/kb/305599. If you are connecting to OLEDB you need to directly connect to database using its credentials. You don’t need to have DSN created for OLEDB.

If you have used ODBC, you need to click on Connect… after creating DSN. When you click on Connect… you will find the following screen:

Choose the Data Source you have just created using DSN. If the Data Source is password protected, enter the User ID and Password. Click on Test Connection… If your Data Source is not password protected simply click on Test Connection… If your connection is successful you will get the following screen.

Voila. Now you have a successful connection.

Here you also have an important option i.e. “Force 32 bit” which you can find below ODBC dropdown in lower panel.

This option allows you to make your DSN compatible with 32 bit. By default ODBC can connect to any 64 bit program but in order to connect to a 32 bit program we need to check this option so that Qlikview can easily connect to any 32 bit program.

So, now coming back to Connectivity part. Since now you have got the message saying that “Connection Test Succeeded”, you need to now connect to the table you want to fetch and get into your Qlikview application.

After receiving the Connection succeeded message, click on OK. You will see the connection string getting added to your script automatically. You see you din’t write a single word for the connection string and yet you got the entire string by itself. Now click on Select… button below Connect… button. This will show you the screen for the tables of the database you just connected to. You will get a window like this

Here Owner will be your schema name. Select on the dropdown for Owner and you will get the list of all the schemas in your database. As soon as you select the owner you will get the list of all the tables in the left panel named “Database tables”.  As soon as you click on any of the table in the Database Tables, you will get the corresponding fields in the table next to it. It gives you an option to select the required fields or all the fields from the table. Once you are done click on OK.

You will again see the entire load statement written automatically in the script window. Now the table has only been added into your script but not in your data model. In order to add it to your data model, you need to click on the Reload button provided below the File menu. Alternatively you can also press Ctrl+R.

Once you press Ctrl+R, you see the reload window with the connection string, the name of the table loaded and the count of records fetched. After the reload is done, you can check your data model by pressing Ctrl+T or clicking File->Table Viewer… Since now we have just one table added to our script, we will see only one table in Table viewer.

Share on Google Plus

About Unknown