Normally selections are made explicitly by clicking on the field values that are interesting. There is, however, also a way to make selections indirectly through semantic links. These are similar to field values, but with the difference that they describe the relations between the objects rather than the objects themselves. They appear as a list of buttons.
When clicking on a semantic link, a selection is made in an other field.
Rules for semantic tables
Semantic links are created by loading tables containing the relations between the objects.
- The table must contain exactly three or four columns.
- A semantic table must either contain relations between field values of different fields or between field values of the same field. A mixture between the two is not accepted.
- The LOAD or SELECT statement loading a semantic table must be preceded by a semantic qualifier to show that it is not a logical table.
Normally four columns are used, the first one containing the field values that have a relation to some other field value and the third one containing the related field value. The second column must contain the names of the relations, and finally, the fourth one must contain the names of the inverse relations.
If three columns are used, no explicit names for the inverse relations can be given. The names given in the second column are used both for the relation and the inverse relation. The names are then preceded or followed by arrows.
If the relations are between field values of the same field, the first and third columns must have the same name. Also the names of the second and fourth column, that is, the type of the relations, must be the same. However, if the relations are between field values of different fields, all columns must have different names.
Extracting a semantic table from data
The semantic table does not always have to exist as a table outside QlikView. It is more flexible to extract this table from the existing table of objects through a separate LOAD statement.
In the presidents example in the QlikView examples directory, the script to generate the links Predecessor and Successor could be:
Directory presidents;
LOAD * from presdnts.csv (ansi, txt, delimiter
is ',', embedded labels);
Semantic LOAD
No -1 as No,
'Successor' as Relation,
No,
'Predecessor' as Relation
from presdnts.csv (ansi, txt, delimiter is ',',
embedded labels) where No > 1;
The second LOAD statement results in a table that looks like the one to the right, and this table is loaded as a semantic table. The where clause is used to omit the first record since this would link the first president to the nonexistent 0:th president.
Note also that this LOAD statement contains two fields labeled No and two fields labeled Relation. Such a LOAD statement would cause a script execution error if used to load an internal table since the load procedure for one single internal table demands that none of the fields have the same name. The corresponding SELECT statement is also not possible, since most ODBC drivers also demand this. Instead, the following structure should be used if the presidents table is in a database:
Connect to DataBase;
SELECT * from presdnts;
Alias No2 as No, Relation2 as Relation;
Semantic SELECT
No -1 as No,
'Successor' as Relation,
No as No2,
'Predecessor' as Relation2
from presdnts where No > 1;
The presidents example is just one simple example of how to use semantic links. These can also be used in genealogy, where the semantic links can be e.g. cousin, sibling, grandmother, etc. or for people in companies where the semantic links can be e.g. superior, reports to, secretary, etc.
Using the related values as relation names
Sometimes it is more descriptive to use the related field value as name of the relation. In the case of the presidents, you may want all the predecessors in one column and all the successors in another:
To create these links, the following script is needed:
LOAD
No as DuplicateOfNo,
FirstName & ' ' & LastName as Name,
*
from presdnts.csv;
Semantic LOAD
No -1 as No,
FirstName & ' ' & LastName as Successor,
No as DuplicateOfNo,
'Dummy1'
from presdnts.csv where No > 1;
Semantic LOAD
No +1 as No,
FirstName &' ' & LastName as Predecessor,
No as DuplicateOfNo,
'Dummy2'
from presdnts.csv;
When a semantic link is clicked, a selection is made in the field of the third column, DuplicateOfNo, which in the semantic table is always the number of the president shown on the semantic link.
It may not be obvious at first, but the inverse relations in the above construction are almost useless. They would show a name of a president and, when clicked, select the predecessor/successor of the shown president. This is why they are called Dummy1 and Dummy2 and only the first relation (column two) is used.
Since we do not want the dummy relations to appear in the list boxes, we must treat the second and fourth columns as different types of relations. This means that the first and third columns must have different field names. This is the reason why we have two columns containing the number of the president, No and DuplicateOfNo.
Two different semantic statements are needed since we want two different list boxes with relations.
This example can also be made with three-column semantic tables, but then the list boxes with the inverse relations will most likely confuse the user.
