Getting Your Data In!
Data is loaded through the script, when you create a new document the wizard will attempt to help you, but it’s always easier to it in the script.
QLIKVIEW IS CASE SENSITIVE, this will catch you out at some point!
When you reload, all data is reloaded. ‘tables’ are essentially dropped and recreated, although there are ways around this
You can work through this page as an end to end example (and I would suggest doing so) and then move onto the other items under ‘getting started’ until you have a complete dashboard.
Note that if you do not have a valid developer license, you can still download and open other dashboards (up to 5 times) but I would suggest building the dashboard as you go, or downloading my complete example at the end *HERE (not yet written)*
Creating Your First Dashboard
OK so I know you’re excited to get on and play so we’ll get right into it and discuss more of the details later.
Create a new file and then immediately save it somewhere.
To access the script either click the script button or go to “File > Edit Script”. You will be presented with a window as below:
The SET statements are important, so leave these in place. Now it is time to decide on your data source. Each of the major ones are covered below.
۱. Writing Data into the Script (load inline)
Sometimes you will just need a quick lookup table which will never change and this is generally where I use this the most.
However for our purposes of a demonstration this will work quite well. Go to Insert > Load Statement > Load Inline you should get the following screen:
You can tab away in here to create your own ‘table’ of data, double click on the ‘header’ row (i.e. where it says “F1″) to give the column a name. The screen behaves quite like excel, you can type directly into a ‘cell’ or into the bar at the top after selecting a cell. Below is the example we will use for now:
Once you hit OK then Qlikview creates the necessary script for you:
Hit your reload button (or if you’re in the main window) to load this data into the file.
You should briefly see the script loading window (which tells you what is loading) and then be presented with a “Sheet Properties” window which you can close by hitting OK.
You should now have a blank screen in front of you with one tab called main. Not very exciting huh?
To see the layout of the ‘tables’ push the table viewer button (You can also go to File > Table Viewer or use Ctrl + T ):
You should see this:
If you want to see the data held, you can get a preview of the first 100 or so rows by right_clicking and choosing preview.
Close the table viewer and go back into the script we want to give our ‘table’ a sensible name and not just the temporary one.
Edit it directly by typing the (very simple) additional line into the script:
You can then reload, and once loaded go back into the table viewer to see our ‘table’ now has a sensible name:
Free Typing an Inline
In this case we can just copy, paste and edit our pre existing script and make a new ‘table':
Once again, reload the dashboard and go into the table viewer.
Now in reality, we would actually just load all this data into one ‘table’ but this will suffice for our example.
Download the sample dashboard up to this point (You will need to have a valid license to open it though)
۲. Importing Data from Excel
To Qlikview, excel is just another ‘flat’ file to import, and as such, these instructions are actually pretty much the same when importing other sources.
In this case we will both sell to our customers and also purchase from them, this is purely to make it simple to show you how to import two separate tabs.
The spreadsheet we will be using can be downloaded by clicking here
Go back into your script editor and choose the “Table Files” button, select your file and choose “open”. This should display this window:
As you can see, Qlikview has detected that it is an excel file, and also that we have embedded labels in the first row. Also note the ‘tables’ drop down box as this is the ‘tabs’ in excel. We will leave this selection on the sales tab for now and just click on ‘finish’. This will create the script for us (although obviously your file link will differ):
Again we need to give this ‘table’ a sensible name by inserting the simple piece of additional text:
Then click reload to bring the data in, you can see in the table viewer that Qlikview automatically makes the links:
We know the data on the purchases tab is in the same format, so we can copy and paste the script just changing the ‘tab’ name:
Now click reload. Note here something odd happens, qlikview sees all the data is exactly the same, and despite the new table name it loads all the data into the sales table (which I didn’t actually expect!)
To get around this, we need to change a field name to ‘trick’ qlikview, so amend the script to look like this, then reload:
Now go and have a look at the table viewer, you can see the system has created a synthetic table.
There is some debate within the Qlikview community as to whether to leave synthetic tables in, as their impact on efficiency isn’t really known that well. Personally I prefer to remove them and use a link table (see later) or work around them.
In this case, we need to alter our import script as below:
We now have a sensible looking table structure:
۳. Importing Data from SQL
This is probably the most used data-source for qlikview. The details here are based on my experiences with MS SQL Server 2005/8, not an ORACLE based system although I know they are very similar.
The great thing with SQL is that you can make IT do all the hard work restructuring data, and leave Qlikview to do the super fast analysis work. Indeed for a lot of dashboards I use in my day to day work we have staging databases that prepare the data.
This element will be a little tricky without access to a SQL database. What I have done, is simply write a script which will create and populate a table on any MS SQL Server, so if you have access to SQL Server Management Studio (henceforth SSMS) then you can use this. If not then I would advise just having a play with your own SQL data. The script can be downloaded HERE.
The code is also here:
CREATE TABLE [dbo].[test_data](
[customer] [varchar] (1) NOT NULL,
[customer_rep] [varchar](50) NOT NULL,
INSERT INTO test_data
So how do we load this data? Its actually quite simple.
Step 1 is obviously to create a connection to the database, ODBC is generally the easiest, although I did find some error with the automatic reload that meant some dashboards I switched to OLE DB.. why this worked I couldn’t tell you!
Obviously you must have the ODBC connection set up on your computer (Although you can set it up via Qlikview and it can be user or system dsn), you can then use the ODBC tool in Qlikview to connect.
In the script editor, look at the data tab at the bottom and select ODBC in the database section, then hit the connect button which will present you with a box. SImply choose the connection and enter the username and password
You will see that this inserts a connection string into the script. NOTE, the script works in the order it is written, and in the order of the tabs.
Therefore you will NOT be connected to the database until the script gets this far. So, I would advise putting this as the first line under the set statements!
Step 2 is to select where we want to load the data from. On the whole, I tend to write the script in SSMS (as you can see the result immediately) and then just copy and paste it into Qlikview.
However, Qlikview also provides you with a very handy tool for creating a simple select. To access this, simply click the ‘select’ button under the connect button. Clicking this will give you another handy box:
In here you can choose your database, select the table and then the rows which you want to return! You can also preview the table, see the data types etc using the different tabs. You can see that I have already selected the items I want, and so if I click on OK it will generate the code for me:
Again this has just created a select statement, but we need to give it a table name and so just amend the code to be:
If we now re-load the dashboard, the additional data will be pulled in.
And thats a wrap for importing data for now! We will look at some more complex cases, such as importing multiple dashboards and using variables to control loads, later on in the process.
For now though, move onto the data display section to get creative