QlikView Data Structure – QlikView Table & Fields
In our last session, we talked about QlikView Box Plot Wizard. Today, we will understand the QlikView Data Structure. Moreover, we will learn QlikView Fields and QlikView Table Name and Labels. Along with this, we will study logical tables in QlikView.
So, let’s start the QlikView Data Structure Tutorial.
2. What is QlikView Data Structure?
The data loaded into QlikView from any type of file is stored in QlikView in a well-structured manner. The data is stored in tables whose structural arrangement makes data processing in QlikView significantly fast. There are different elements of the entire QlikView data structure which will discuss in the sections to come. It is very important for the data residing in QlikView to well-structured because if not, processing the information or data values for analysis and calculation becomes a tedious task and effects both speed and efficiency of the tool.
Let us start our lesson from the very initial steps of loading data into QlikView.
Data from any data source loaded using two script statements i.e. LOAD and SELECT which creates an internal table for and containing the data being loaded. A table is a combination of rows and columns where each row (Record) contains an object type and the column (Field) contains specific property or attributes of the object type.
a. Rule of Data Loading
There is a certain set of rules defined for loading data into QlikView using LOAD and SELECT statements.
The first rule is that the tables loaded using LOAD or SELECT or a mix of both doesn’t pose a difference on the table being loaded. All the three ways of loading data are the same and tables loaded using them are also same.
The second rule says that the order of fields both while loading in a statement and after being loaded in memory stays arbitrary. The order of fields does not set in a certain way.
The field names (columns) of tables loaded into QlikView’s memory are case-sensitive and are used for data associations or in functions in the script.
b. Script Execution
The order of events taking place during the execution of a script in which tables are being loaded using LOAD and SELECT statements represent below.
3. QlikView Fields
Fields are the most important entities in a table. The QlikView field is known to be the data-carrying entities because it is in respective fields that all the data values corresponding to that field reside. When we talk about loading or extracting data from the data file, the data contained as data values in data fields actually get retrieved. Like the rows and columns in a table, records and fields are in a QlikView table respectively. An information contained in a data field may sort in any number of fields as per the requirement. As we know, the file data can extract using LOAD and SELECT statements or even the Binary statement in some cases.
In QlikView, the data once loaded structured as field and records cannot manipulate when they are displayed at the front end as sheet objects by the user. Any changes that the user wishes to bring in the table and its filed can only be done through the script and then reloading or re-executing the script. The field values have strings, numeric or alphanumeric data types which exist in the dual format so that they can use in calculations and sorting in future.
A detailed information about the loaded tables and their field structure view from the Tables tab in the Document Properties dialog box in QlikView. It can invoke from the Settings menu or by pressing Ctrl+Alt+D. It gives information like table list, number of fields (columns), number of records (rows). You can select/deselect the fields you want to define as Dimensions or Measures. Also, you can export an existing data structure if you want.
a. QlikView Field Tags
In QlikView, field tags use to add metadata to already existing fields. You can add either System field tags or Custom filed tags in QlikView. The prefix to system tags is ‘$’ and they generate automatically during script execution by the system. On the other hand, custom field tags can create using Tag statement by the user in the script.
QlikView generated system filed
A key field linking two or more tables
Part of one or more synthetic keys
The field for a synthetic key
These fields are hidden and can only use through expression variables like HidePrefix and HideSuffix in the script.
All field values in a table are numeric (except for NULL)
All field values are integers (except for NULL values)
All fields except for numeric
Field values containing only standard ASCII characters
All field values except for NULL values can interpret as dates.
All field values except for NULL values can interpret as a timestamp (date and time)
b. System Fields in Qlikview
Apart from the QlikView fields generate to structure data extract from external sources, QlikView also generates some fields during script execution known as System Fields. These QlikView fields not used by the user but kept by the system as a structure and design aid. As we saw in the previous section, system fields also prefixed by the sign ‘$’.
You can view these system fields by right-clicking anywhere on the sheet. Then click on Select Fields… , a dialog will open whereupon checking the Show System Fields option all the system fields will display.
The table given below has a brief description of the common system fields displayed.
$Table: Displays all internal tables loaded during executing the script.
$Field: Displays all the fields that are read from the tables.
$Fields: Shows the number of fields in different tables.
$FieIdNo: Shows the position of the fields in the tables.
$Rows: Shows the number of rows in the tables.
$Info: Displays the names of the info tables if included in the document.
Also, a System Table can generate that shows all the details of the available table in that document and their Dimensions and Measures. It can create through selecting Layout>New Sheet Object> System Table.
c. Input Fields
As we know that in QlikView, data field values cannot edit in the sheet objects like a table box, chart, list box etc. It can only edit either at the source or in the script. However, there is a feature or a script statement rather, that allows you to edit the data values in the sheet objects. This feature is called the INPUTFIELD statement. You have to define the table fields which you want to keep in input edit mode using this statement before defining the LOAD statement. One thing to keep in mind is that this feature is not applicable on the key fields.
Once you reload the script using the Inputfield statement you will see input icons in front of each field value in the selected field. Upon clicking on this icon, the value or matter of that cell can change. You can also associate an expression so that the values you enter gets evaluated according to that expression condition.
Let us see how the input field works with the help of an example.
Suppose we have some data related to selling price and quantities of the product sold (refer to the image below).
Now we need to input the values in the field Input price and calculate the changes occurring through the changed price in final sales amount. We will do so by making the field ‘Input Price’ an input field. This will do with the INPUTFIELD statement. Save the changes in the script and reload the script (Ctrl+R).
//FROM //[C:\User\Qlikpower\Download\qv dummy cube.xlsx] // (ooxml, embedded labels, table is Sheet1); INPUTFIELD [Input Price]; LOAD [Product Code], [Selling Price], [Input Price], Quantity FROM [C:\User\QlikPower\Desktop\Price List.xls] (biff, embedded labels, table is Sheet1$);
QlikView Data Structure – Script
An input function InputSum can apply on the Input Price field. So that it can carry out some calculations.
Now, in the table, the input mode has been activated in the selected field. You can edit the cells using the input icons.
In the sample data, when input price values have been entered and the other values like ‘price difference’ and ‘sales amount after price change’ are recalculated and displayed.
4. QlikView Table Names and Labels
When a table is loaded in QlikView from a data source, the table can name using labels or other naming methods in the script. The tables can label in the script in the way given below,
LOAD a,b from c.csv;
LOAD x,y from d.csv where x=peek(‘a’,y,’Table1′);
Here, the table will name as Table1 by writing ‘Table1:’ at the start.
If the user does not mention a label, then the file name from the source file take mention in the From statement.
In case of Inline tables, the labels generate as INLINExx where xx is the serial number in the order the tables create. Like the first table has INLINE01, for the second table, it will INLINE02.
Similarly, tables generated using Autogenerate are named as per the format AUTOGENERATExx. Like, AUTOGENERATE01 and so on.
If some name repeats, then one of them named by the adding the suffix ‘-x’. For example. Sales-1, Sales-2 etc.
5. Logical Tables in QlikView
Logical Tables are the tables that follow a certain logic that defines how the data values and fields will behave and get associated with other fields in other tables. Every table loaded in QlikView will be one or the other type of a logical table.
Two tables join keeps the data records of each, intact.
Create by changing the orientation of rows and columns in a standard table.
Such tables make by splitting one table into several different logical tables.
Such tables contain information about external sources like files, sounds, URLs etc, hence, they call as information tables.
Creates by interpreting numeric intervals present in the fields or columns of other tables.
A table creates joining previously loaded or pre-existing tables in QlikView.
A table creates having only the common fields between two or more tables.
Creates a copy or maps already existing fields in a new table.
|Semantics||Such tables store the information on the relations between different tables such as predecessor, successor etc. Such tables call as semantics tables.|
6. Optimal Data Structure in QlikView
In QlikView, although the system manages the data being loaded into QlikView in terms of its structure and layout, there are still certain practices the users need to follow to ensure optimal functioning of the software.
If the tables do not link and structure properly, then the consequences will see by the inefficient working of QlikView. If the QlikView data structure is in the form of a snowflake, then it will pose many difficulties in processing the data and tracking associations of data values between multiple tables. QlikView Data structured in the snowflake design shows below.
Instead of data being in a snowflake structure, it should structure as the star schema.
Also, for QlikView data structure optimization, a formation of synthetic keys must avoid. Synthetic keys create when there are one or more common fields between two or more tables.
So, this was all about QlikView Data Structure Tutorial. Hope you like our explanation.
Hence, in this lesson, we learned about the basics of QlikView data structure and topics related to it. We saw the rules of data loading, a process of script execution, about two different types of fields, field tags, naming tables, logical tables and at last, how to optimize data structure in QlikView. Furthermore, if you have any query, feel free to ask in the comment box.
Related Topic – QlikView Master Calendar