Saturday, 28 April 2012

How to connect to an SQL Server


So my first post on the all new blog, I'm by no means a Qlikview expert but I have been using it for two years now and can find my way around.

What I'm hoping to do with this blog is compile a list of how to do everything in Qlikview, starting right at the beginning.

The qlikview training is great, however Qliktech seems very focussed on using files as a datasource.  I know the first question I asked after completing the tutorial was, how do I connect to a database?


Although there are plenty of ways to connect, here's how I do it.

1. Create a new Qlikview document, the built in wizard will then appear, I've never used this so just hit cancel


Does anyone use this?





2. Go to file > Edit script 
#Protip: Keyboard shortcut Ctrl+E




3. This will open the script tab, if you don't have any experience coding, DON'T PANIC! It's a lot simpler than you think
4. At the bottom of the script tab, are four tabs Data, Functions, Variables and Settings.  If you have an ODBC connection already, just click connect and select it, then skip to step 7.  If you don't have ODBC set up or are unsure what I'm talking about select "OLE DB" in the drop down and click connect.
#Protip: If using a mixture of 32 bit and 64 bit environments, untick the "force 32 Bit" field

5. Select the provider you want to use, in this case SQL Server, then click Next
6. Enter the server name, choose whether to use windows security or a username and password.  Then select the database on the Server.  Click Test Connection and if all good click ok.
7. Qlikview will then generate the connection string into the script window
Now on the Data Tab, click Select

8. You can then choose whether to use table or views, if you can't find what you're looking for check the Owner 
#Protip: click Preceding Load and Qlikview will list all fields in the script window for future use.

9. and that's it! your script is done


Click OK, save the document and go to File>Reload

#Protip: Keyboard shortcut Ctrl+R

My next post I'll be looking at displaying data - I'll be using UK government data from here