SET NullInterpret='';
IntervalTable:
LOAD Key, ValidFrom, Team
FROM 'lib://dataqv/intervalmatch.xlsx' (ooxml, embedded labels, table is IntervalTable);
Key:
LOAD Key,
ValidFrom as FirstDate, date(if(Key=previous(Key),
previous(ValidFrom) - 1)) as LastDate,
Team
RESIDENT IntervalTable order by Key, ValidFrom desc;
drop table IntervalTable;
Transact:
LOAD Key, Name, Date, Sales
FROM 'lib://dataqv/intervalmatch.xlsx' (ooxml, embedded labels, table is Transact);
INNER JOIN intervalmatch (Date,Key) LOAD FirstDate, LastDate, Key RESIDENT Key;
Thenullinterpret statement is only required when reading data from a table file since missing values are defined as empty strings instead of NULL values.
Loading the data fromIntervalTable would result in the following table:
Thenullasvalue statement allows NULL values to map to the listed fields.
CreateKey, FirstDate, LastDate, (attribute fields) by using previous and order by and thereafter the IntervalTable is dropped having been replaced by this key table.
Loading the data fromTransact would result in the following table:
Theintervalmatch statement preceded by the inner join replaces the key above with a synthetic key that connects to theTransact table resulting in the following table:
6.16 Creating a date interval from a single date
Sometimes time intervals are not stored explicitly with a beginning and an end. Instead they are implied by only one field – the change timestamp.
It could be as in the table below where you have currency rates for multiple currencies. Each currency rate change is on its own row; each with a new conversion rate. Also, the table contains rows with empty dates corresponding to the initial conversion rate, before the first change was made.
This table defines a set of non-overlapping intervals, where the begin data is called “Change Date” and the end date is defined by the beginning of the following interval. But since the end date isn’t explicitly stored in a column of its own, we need to create such a column, so that the new table will become a list of intervals.
Do the following:
1. Create a file called Rates.xlsx containing the table shown above and store it ready for loading.
Make sure that the dates in the Change Date column are in the same format as the local date format.
2. Determine which time range you want to work with. The beginning of the range must be before the first date in the data and the end of the range must be after the last.
3. Load the source data, but change empty dates to the beginning of the range defined in the previous bullet. The change date should be loaded as “From Date”.
4. Sort the table first according to Currency, then according to the “From Date” descending so that you have the latest dates on top.
5. Run a second pass through data where you calculate the “To Date”. If the current record has a different currency from the previous record, then it is the first record of a new currency (but its last interval), so you should use the end of the range defined in step 1. If it is the same Currency, you should take the “From Date” from the previous record, subtract a small amount of time, and use this value as “To Date” in the current record.
The script listed below will update the source table in the following manner:
The Qlik Sense script looks like this:
Let vBeginTime = Num('1/1/2013');
Let vEndTime = Num('1/3/2013');
Let vEpsilon = Pow(2,-27);
Tmp_Rates:
LOAD Currency, Rate,
Date(If(IsNum([Change Date]), [Change Date], $(#vBeginTime))) as FromDate From 'lib://Tutorial Files/Rates.xlsx'
(ooxml, embedded labels, table is Sheet1);
Rates:
LOAD Currency, Rate, FromDate,
Date(If( Currency=Peek(Currency), Peek(FromDate) - $(#vEpsilon),
$(#vEndTime) )) as ToDate Resident Tmp_Rates
Order By Currency, FromDate Desc;
Drop Table Tmp_Rates;
When this script is run, you will have a table listing the intervals correctly. Use thePreview section of the data model viewer to view the resulting table.
This table can subsequently be used in a comparison with an existing date using theIntervalmatch methods.
6.17 Hierarchies
Unbalancedn-level hierarchies are often used to represent among other things, geographical or
organizational dimensions in data. These types of hierarchies are usually stored in an adjacent nodes table, that is, in a table where each record corresponds to a node and has a field that contains a reference to the parent node.
In such a table, the node is stored on one record only but can still have any number of children. The table may of course contain additional fields describing attributes for the nodes.
An adjacent nodes table is optimal for maintenance, but difficult to use in everyday work. Instead, in queries and analysis, other representations are used. The expanded nodes table is one common representation, where each level in the hierarchy is stored in a separate field. The levels in an expanded nodes table can easily be used e.g. in a tree structure.Thehierarchy keyword can be used in the data load script to transform an adjacent nodes table to an expanded nodes table.
Example:
Hierarchy (NodeID, ParentNodeID, Title, 'Manager') LOAD NodeID,
ParentNodeID, Title
FROM 'lib://data/hierarchy.txt' (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
A problem with the expanded nodes table is that it is not easy to use the level fields for searches or
selections, since a prior knowledge is needed about which level to search or select in. The ancestors table is a different representation that solves this problem. This representation is also called a bridge table.
The ancestors table contains one record for every child-ancestor relation found in the data. It contains keys and names for the children as well as for the ancestors, that is, every record describes which node a specific node belongs to. Thehierarchybelongsto keyword can be used in the data load script to transform an adjacent nodes table to an ancestors table.
6.18 Loading map data
To be able to create a map visualization, you need to load point data or area data.
You can load map data from KML files and Excel files. You load the data either through the data manager or the data load editor. By default, all fields are selected in the data selection dialog, even if they do not contain any data. A KML file could contain, for example, area data but no point data. Although there is nothing wrong with loading empty fields, it can be inconvenient to have empty fields when you are working with the map visualization. Adding a dimension without any data to a map will not generate any visual output and could confuse a user. Therefore, you should clear the selections of all empty data fields in the data selection dialog, before you load the data.