The beginning

Qlikview is an awesome product that I am continually trying to wrap my brain around. I find writing guides and tutorials is a good way to force myself to learn stuff so it here it goes.

Fundamentals

Database: A database is simply a container of information stored in a structured format. Database is often used interchangeably with the term table.

Unlike databases, QlikView does not allow explicit (very specific) definitions of table relationships. In a database these would happen through key fields. (The field that links tables together)

QlikView automatically defines table relationships (associations) through fields that are named the same. Any fields with the same name (case sensitive) form a key field that results in an outer join between the tables.

So there are a few tricks you need to perform with QlikView. If the fields are already named the same and that's the relationship that you want, then you are set.

If the fields are not named the same then you need to name them. (you must rename one of the fields in the script using an alias statement or an as clause)

And if relationships are happening that you don't want then you need to prevent them.

Association in QlikView is basically the same thing as a SQL outer join. With an outer join, rows are returned even when there are no matches through the JOIN critieria on the second table.

Let's get our feet wet. We first want to load some data into QlikView. You can load in a ton of different file formats into QlikView. Let's go file new, and create a new QlikView Document.
Now hit Cntrl+e to edit the script.

You now are looking at the Edit Script window. You can reload your data, or debug your loading, or add new tabs (Script is executed left to right, tab by tab)

At the bottom is a toolkit for generating load scripts. It's divided into three sections, getting data from a Database, data from files, or inline data.

One handy thing to note is the relative path check box, this allows you to reference files without having to use absolute paths i.e. file.csv instead of c:\myfolder\qlik\file.csv

Ok, let's look at some script syntax. 'Connect' is your friend.

• Connect creates a database connection, either ODBC or OLE DB.
• Once you are using a 'connect' it is used until a new 'connect' is defined.
• If you use the wizard and select 'scramble connect user credentials' then it will scramble it. If you enter it in manually, you must use the unscrambled.

Example:
ODBC CONNECT TO [CSMR SQL Server] (XUserId IS IJQNUZFMDA, XPassword IS KBYbFSJMNB);

So, first you Connect and then you Select

• The select statement is used to identify fields and tables to load from the currect DB connection.
• Any valid select statment is legit, but... some ODBC drivers can impose limitations!
• Select statements cannot use QlikView functions!
• Field names and table names must be bracked by quotes or square brakcets if they contain spaces or special characters!
• distinct, as, where, group by, order by, or union are sometimes not supported.

Ok, lets put this into practice. Let's make the most boring QlikView possible. Open up notepad and then type 1 and then save the document as c:\Data.txt

Now, open up Qlikview, go file, new document. Hit control + e to edit the script, hit the table files button, navigate to your data.txt, then change the Options ->Labels to None and then hit finish.

Your script should be something like

LOAD @1
FROM C:\DATA.txt (ansi, txt, delimiter is '\t', no labels, msq);

Then hit OK. Now hit Control + R to reload in all that fantastic data. It will ask you to save, so save it as something. Now right click anywhere on your blank page and choose 'New sheet object, New List Box' It will default to using '@1' as your field then hit Ok. You now have a list box with the label of '@1' and the value of 1. Tada, your first QlikView. Pretty boring huh. Well click on the 1, wow it turns green. Yup still pretty boring! But ya gotta start somewhere.