Lesson 3 – load and document data

Overview

In this lesson we are going to learn the most important rules of importing data. This is just a recommendation, not a “must”!

Documentation – comments

Th emost efficient form of providing your logics and thoughts to your fellows is to wirte comments within your scripts. You can write these at any point in your script with using the follwoing syntax:

  • REM – before a command causes the script to complete ignore the command if it gets executed
  • // – at the start of a line or at any point in a line causes the script to ignore every character behind it in that particular line
  • /* … */ – causes the script to interpret text between /* and */ as a comment

Since we will later integrate our scripts into a version management and thus have the possibility to use documentation generators, it is recommended to use the following conventions.

Clean loading of data

In the following, I show how a qualified data source is generated from a “raw” data source:

The data in the table structure display with the table name “Test.Customers” appears. However, we want to name our table with “Customers”.

After the table now has the name that we want, and we later want to define possible keys for further tables – we will still qualify them.

Self-determined foreign relations

You want to load multiple data tables. But in every table are multiple identical field names. If you wouldn’t qualify the table, Entity Relationship Mapping of QlikView would automatically connect these “identical” fields. Which can be prevented with the just learned qulaification. But you still want to connect the tables over a user-determined field. We use the possibility to “exclude” certain field names via a patterns while working on the qualification. A good method is to mark the key fields with the prefix “%”. The following example shows the logic for connecting to tables:

We have now connected both tables with the field <%CustomersId> and no additional “wrong” connection via <Info>.

QlikView Debugger

When developing the script logics, it does not always help to load the entire data at the beginning and to get information about the current progress of the script in parallel. Script commands and variables can be monitored.

Screenshot 2015-03-25 20.37.54

Limit the data for the debugger so that possible relations between 2 tables also have one hit in the key. This allows the data model to be validated in the interface by means of a simple cross-tab in advance.

In addition, you can activate the “log” function for your document under the properties. I recommend to enable this options during the development process. After loading the script, you will find in the same folder as your QlikView document is a MyFile.log which provides further detailed information for error analysis.

Most importan data objects for presentation

In order to present the loaded data we will learn many possibilities in later lessons. At this point, we learn 2 objects that are relevant at this time:

  • List Box (List Box)
  • Diagram (Chart)

You can add them by clicking on the corresponding icons in the Design Toolbar or by right-clicking on an empty space in the workspace and clicking on “New Object” in the context menu and selecting one of the two.

The ListBox is used to filter the data existing in the data model.

The diagram is a “pivot” cross table, which allows to calculate values by means of aggregation functions by category. The most well-known are:

  • SUM(Field name) – Sum of the values
  • AVG(Field name) – Average of all values (Attention often wrong, since no weighting)
  • COUNT(Field name) – Anzahl aller Werte (Mehrfachzählungen)
  • MAX(Field name) – Höchster Wert (numerisch)
  • MIN(Field name) – Niedrigster Wert (numerisch)

Exercises

  • Create a new empty QlikView document
  • Opne the script-editor
  • Document your first QlikView script
  • Connect to a data source of your choice
  • Find the number of loaded records and output them using a diagram

Leave a Reply