QlikView Database Connection | QlikView Inline data
In our last tutorial, we talked about QVD Files. Today, we will discuss QlikView Database Connection & Inline Data. In this QlikView Inline Data tutorial, we will explore steps for a data connection in QlikView. At last, we will cover QlikView Inline Data.
So, let’s start the QlikView Database Connection.
2. QlikView Database Connection
QlikView can connect to many different data sources or databases like MySQL, Oracle, SQL server etc. Once the database from these external files is loaded into the in-memory of QlikView software. This stored data can be quickly retrieved for further analytical purposes. Let us see the QlikView Database Connection. Before we move on, you might need to make sure that you have a pre-existing MySQL database in your computers.
- In order to connect with a MySQL database, you need to follow some steps. You can connect through the ODBC connection which connects to DSN (Data Source Name) and then the DSN connection fetches the MySQL database.
- If you do not have a DSN connected to MySQL database you can do that online.
Before we explore this, let’s revise What is QlikView
Moving on with the steps for QlikView Database Connection,
a. Database Connection
Open the script editor in QlikView and click on the connect option after choosing ODBC in the database option. A window will appear showing all the existing databases. Select MYSQL userdsn (DSN) and click on the test connection. If your connection goes okay, then a message prompt saying Connection Test Succeeded will appear.
Let’s discuss the Architecture of QlikView
b. Connection Verification
Next, we need to check whether our connection with the database was successful or not. For this you need to notice the script editor window, which will show confirmatory text on the screen. Now click on Select below the Connect tab to continue.
SET DateFormat='M/D/YYYY'; SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'; SET MonthName='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'; SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'; ODBC CONNECT32 TO mysqluserdsn;
c. Selecting Database Table
Here the database name is Sakila which is in the Data source file that we selected earlier. It has got a list of table databases to select from. We have chosen actors. You can also choose any other database from the drop-down list. Then click OK to continue.
d. Table Loading Script
Now that the table titled actor is been selected it starts showing in the script editor as well.
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'; SET MonthName='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'; SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'; ODBC CONNECT32 TO mysqluserdsn; SQL SELECT * FROM sakila.actor;
e. Storing Loaded Data
We will have to type some commands to store our data permanently. We give all the important information through this command and define a path for the file. The extension to this file will be .qvd. Next, we save this file with a suitable name in .qvw format.
ODBC CONNECT32 TO mysqluserdsn; actors: SQL SELECT * FROM sakila.actor; store actors into E:\Qlikview\data\actors.qvd;
f. Creating the Table
The final .qvd file can be retrieved or reloaded in the main document by pressing CTRL+R. You can now select a straight table to visualize the data in a tabular form with all required columns and rows.
3. QlikView Inline Data
Inline data features allow you to directly enter data into QlikView from a clipboard. Let us learn how to do it. First of all in a new QlikView document open the script editor. Go to Insert > Load Statement > Load Inline.
a. Inserting Data
An excel like spreadsheet will open in QlikView itself and you can enter data there as shown in the image below.
b. Loading Script
Once you did with making all the entries (we have entered an electronic store record), click on OK. As soon as you do this all your entries will appear on the script. Save this file with a suitable name (will be saved in .qvw format) and click on OK.
SET FirstMonthOfYear=1; SET CollationLocale='en-US'; SET MonthName='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'; SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December'; SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'; SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday' LOAD * INLINE F1, F2, F3 Customer id, Item, Price dx600, Fridge, "70,000/-" dx890, TV, "1,00,000/-" dx788, AC, "40,000/-" dx654, Laptop, "32,000/-" dx566, Camera, "20,000/-" dx729, TV, "55,000/-" dx899, AC, "50,000/-" dx899, Stereo, "25,000/-" dx328, TV, "35,000/-" dx425, AC, "42,000/-" dx190, Phone, "40,000/-" dx006, Laptop, "47,000/-" dx101, Fridge, "60,000/-" dx055, Microwave, "22,000/-" ];
c. Creating Table Box
On the main document in QlikView, retrieve the saved file through CTRL+R . Then go to LAYOUT and select a NEW SHEET OBJECT then TABLE BOX. Add fields you wish to be displayed on the table and click on OK.
Upon clicking OK you will get to see a table with all the entries that you did.
So, this was all about QlikView Database Connection. Hope you like our explanation on QlikView Inline Data.
Hence, we are pretty sure that after learning to work on QlikView this much you must not be feeling daunted by this tool anymore. Be confident and you will become master of this tool in no time. So, we have seen how to load files like XML, Web, Delimited and Excel files. Here, we learned QlikView Database Connection and QlikView Inline data. Furthermore, if we miss something, do let us know in the comment section.
Stay tuned and keep practising.
Related Topic – QlikView Careers