Databases Reference
In-Depth Information
Note that we have defined a table name, at the beginning of
the query, so that we can use it to reference the table later on.
We have also renamed the
Amount
field to
InvoiceAmount
.
2.
Next, we add a subsequent query, in the same script, to access the table
already in RAM using the
Resident
keyword. In this case, we will also
aggregate the data using a
Group By
clause, which is a data transformation
technique explained later in this chapter.
SalesTotals:
LOAD
Department,
Sum(InvoiceAmount) as TotalAmount
Resident SalesData
Group By Department;
Note that, when referencing a table that is now part of the QlikView data model,
we must use the field names with which they have been defined, which might
not necessarily be the same names as in the source table. In this case, we are using
InvoiceAmount
, a name that was defined in the previous query. The same applies
for table names.
As a result, we will have two tables in our data model; one with all the data at an
atomic level, the product of the first query, and the other as an aggregated version of
the
SalesData
table with totals by
Department
, the product of the
Resident
load we
constructed in conjunction with the
Group by
statement.
Aggregating data
While QlikView shines in dealing with massive data volumes, sometimes we just do
not need to load everything at an atomic level. Data aggregation can, for example,
be used in deployments where document segmentation by detail is needed, in which
case two documents are created to serve different user groups and analysis needs:
one document will have all data with the highest level of detail and another one will
have a similar data model but with aggregated (reduced) tables. This way, users are
better served by keeping a balance between performance and analysis needs.
In this section, we will implement a document segmentation scenario by aggregating
the
Flight Data
table to create a second document intended for executive users, who
only require summary data.
