I am in the process of learning about QlikView, an awesome data visualisation application. It has the ability to connect to diverse data sources: anything from a CSV file to a database table.
This is how to connect QlikView to a MySQL database using a ODBC on a local dev machine.
An ODBC connector works as a standard gateway for programming languages, applications (such as Excel, Access, or OpenOffice) to communicate with database server with ease.
To dynamically connect to and query a MySQL database table directly from QlikView Personal Edition:
- Download and install the MySQL ODBC connector: mysql-connector-odbc-5.3.4-winx64.msi [https://dev.mysql.com/downloads/connector/odbc/]
- Create a DSN — I used this excellent tutorial: http://www.plaintutorials.com/install-and-create-mysql-odbc-connector-on-windows-7/
- Open QlikView, and open the Edit Script window:
File >> Edit Script
- Place cursor end of file (there will be some default config info)
- At the bottom of the window, on the Data tab, select ODBC from the Database select option, and click the Select… button and select the DSN you created from above from the Data Sources select — you can click Test Connection to check that you have entered the correct details
- Click OK
- This will open the Create Select Statement window:
- the Data source should be set to your DSN, and the Database select should be populated with your the databases
- Select the relevant database; the Database Tables and Fields boxes will be populated, and a SQL SELECT statement will be visible at the bottom in the Script tab
- Click OK, and new lines will be added to the script window which define a connection string and a SQL statement
- Click Save and OK
File >> Reload, and a Script Execution Progress window will pop-up and you will see the script connect to and query the database using your DSN connection
- You are now ready to add data to your project!