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.
/** * @author Philipp Frenzel <firstname.lastname@example.org> * @verison 1.0.0 * @copyright Frenzel GmbH 2015 * @todo This needs to be done - pls. follow up * * Here we describe what we are doing with the next codeblock */
Clean loading of data
In the following, I show how a qualified data source is generated from a “raw” data source:
//connect to the data source OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=Test.mdb]; //Example raw data LOAD Name, Surname, Address; SQL SELECT * FROM Customers;
The data in the table structure display with the table name “Test.Customers” appears. However, we want to name our table with “Customers”.
//loading Example raw data in table customers Customers: LOAD Name, Surname, Address; SQL SELECT * FROM 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.
//loading and qualifying example raw data in table customers QUALIFY "*"; Customers: LOAD Name, Surname, Address; SQL SELECT * FROM Customers; UNQUALIFY "*"; //internal field names are now Customers.Name, Customers.Surname, Customers.Address
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:
//loading and qualifying example raw data in table customers QUALIFY "*"; UNQUALIFY "%*"; Customers: LOAD Id AS %CustomersId, Name, Surname, Info; SQL SELECT * FROM Customers; Addresses: LOAD Id, CustomersId AS %CustomersId, Address, Info; SQL SELECT * FROM Addresses; UNQUALIFY "*"; //internal field names are now %CustomersId, Customers.Name, Customers.Surname, Customers.Info, Addresses.Address, Addresses.Id, Addresses.Info
We have now connected both tables with the field <%CustomersId> and no additional “wrong” connection via <Info>.
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.
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)
- 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