Let versus Set (Again)

SET vString = 1 + 1; // The string "1 + 1";
LET vEvalute = 1 + 1; // The value 2

You can get very fancy using variables in Qlikview. You can include arguments inside your variables, so that you can use them like user defined functions.

So, if you wanted to create a 'function' to multiply two numbers you could use a Set like this

SET DOSTUFF = '$1*$2';

The $1 and $2 act as arguments. (Think %1 for a DOS .BAT file)

If you use a SET, it will plug in the arguments and evaluate as a string. i.e.

SET A = $(DOSTUFF(2,2)); // returns '2*2' in A

Note: The dollar sign in front of the DOSTUFF 'function', this signifies Dollar-Sign Expansion, basically some sort of text replacement happens between the parenthesis before it is evaluated.

So, if we instead use a LET

LET A = $(DOSTUFF(2,2)); // returns 4 in A

Applying formatting to a dimension

The trick is that you have to remember to use a calculated dimension. And then,

=num([Field],'######,###.00')

Quick Tip

For some reason I must have failed to learn that you can move sheet object using the arrow keys in QlikView

+ arrow key -> 1 pixel at a time
+ -> 10 pixels at a time

Part 4, the magical list box

The list box is the fundamental QlikView component.

All data loaded into a QlikView can be displayed in list boxes.

If a value occurs more than once in loaded database, only displayed once in list box

A list box can show the frequency of the number of times the value occurs in the DB.

Part 3

A Little bit more about the QlikView associative database.

There are differences between how QlikView manages join relationships between tables and how a typical DB might handle joins.






You might be tempted to think that QlikView has a Categories.CategoryID field and a Products.ProductID field

But a better visualization is with a categoryID field stored between the two tables.










Qlikview combines the distinct datapoints into a single field. This is a Key Field

A reference is maintained through small binary pointers maintaining the relationships back to the original tables.



Compresses data by 80-90%, to 5-20% of its original size. Which is then loaded into memory.

Part 2, comments, loading a .csv

Working with scripts.

It's always useful to know how to add some comments to your scripts. So here are the three ways.

Using REM (end it with a ;)

REM this is a two lined comment
because it needs to end with a semi-colon;

using //

//this is a comment
//so is this

Using /* ... */

/* this is
a longer
comment
the end */

I'm lazy so now I'm just going to base some of this off of this tutorial

So let's load in their nifty .csv file. It's the Country1.csv. Click the Tables Files button on the edit script screen, then browse to the Country1.csv and then you will notice it automagically detects that it is comma delimited so then just hit finish.

Should look like

LOAD Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
FROM [C:\Tutorial_English v8\Application\Data Sources\Country1.csv] (ansi, txt, delimiter is ',', embedded labels, msq);

You will notice that this is an absolute path. If you delete this script, and then check the Relative Paths checkbox and rerun the wizard, you will notice it will generate a script like this.

Directory;
LOAD Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
FROM Country1.csv (ansi, txt, delimiter is ',', embedded labels, msq);

Notice the Directory statement. A directory statement without a following path is going to search the same dir where your .qvw is saved to. You can also specify a directory to look in by typing

Directory c:\userfiles\data;

Ok lets get tricky, now lets say we wanted to use a variable to load our .csv

let myCSV = '[C:\Tutorial_English v8\Application\Data Sources\Country1.csv]';

LOAD Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
FROM $(myCSV) (ansi, txt, delimiter is ',', embedded labels, msq);

That's kindof silly though, but doable.

Ok, so let's say we ran the script of one of those versions. If you are following the tutorial we should addArea(km.sq), Capital, Currency, and Population(mio). So now you can see the real fun of qlikview. For example, you can click on amsterdam and see that it has a population of 15.2 million and uses the Euro. And if you clear your selections (hitting the clear button) then you can click on the 15.2 and it will show you the reverse that the capital with a population of 15.2 is amsterdam. You can also clear all of your selections and then let's say click on the Euro and it will show you all the capitals. Click around, get a feel for how the data is connected. Isn't that marvelous!

Part 1

So, after executing a Connect and a Select you can also use a Load statement.

You can load directly from a file, You can load from a subsequent (following next) select or load statement, but it must follow immediately after. Can load from a previously loaded (resident) table, load directly from an inline load, or load from generated data.

A quick Inline example is
Load * Inline
[CatID, Category
0,Regular
1,Occasional
2,Permanent];

In the Load script it is possible to rename one or more fields.

Since QlikView puts a great deal of meaning into names (for associations) you should really learn these!

Load as: means rename a specific field in that specific statement.

Alias as : means you rename all the occurrences of those fields with the names specified.

Rename Field: Rename field XAZ0007 to Sales; (It can also use a mapping table)

Now for my oh so awesome example.

Create our favorite c:\Data.txt with notepad and type in

StupidName
1
2
3
4
5

So now our load statement would look like this (to change StupidName to BetterName)

LOAD StupidName as BetterName
FROM C:\DATA.txt (ansi, txt, delimiter is '
', embedded labels, msq);

or using Alias (Notice the Alias comes first!)

Alias StupidName as BetterAliasName;

LOAD StupidName
FROM C:\DATA.txt (ansi, txt, delimiter is '
', embedded labels, msq);

Or using rename Field (Notice the rename goes after!)

LOAD StupidName
FROM C:\DATA.txt (ansi, txt, delimiter is '
', embedded labels, msq);

rename field StupidName to aRenamedName;

And using a mapping table (Map comes first!)

MyMap:
mapping load * inline [
Old,New
'StupidName', 'aNewMappedName'
];

LOAD StupidName
FROM C:\DATA.txt (ansi, txt, delimiter is '
', embedded labels, msq);

rename fields using MyMap;

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.