The first lesson in advanced data transformation will be about optimizing loads when processing data. Now, we will describe yet another way of reading source tables, but this time the "source" will be QlikView itself. There are different cases in which this approach will prove useful and we will describe two scenarios to perform it:
This concept refers to the ability of replicating the data model of an already created QlikView document and placing it into another QlikView document without accessing the original data source. In technical terms, it's a Binary load. Once the data model is cloned by the second QlikView document, it can be manipulated further, integrated into a bigger data model (that is, adding more tables to it), or even reduced by removing some of its tables or data.
Suppose we have a QlikView file, with an already constructed data model and all of the composing tables properly associated. We now want to use this same model in another QlikView document, adding just a few more tables. The process for binary loading a QVW is as follows:
Binary [file name.qvw]
;In the environment we've been working throughout the book, can you find a use case for a Binary
load? Which would it be?
I can think of one. For example, we have an initial QlikView document with the Airline Operations
data. Based on this initial data model, we can create a new QlikView document, to which we will only add the Employment
data. As a result, we would have one Airline Operations
document accessed by certain users, and another with the same data but with additional information about airline employment, which might be treated as confidential and accessed by another group of users.
As a side note, we must point out that binary loads are used in yet another approach to data architecture. We will not discuss it in-depth, but suffice to say that the new layer is composed of QlikView documents consisting of only a data model without any frontend objects, referred to as "QlikMarts". These QlikMarts then become the source for the QlikView documents in the Presentation Layer.
In some cases, we will need to read the same table more than once in a single script execution. This means, querying the database (or QVDs) and pulling data from it, and then reprocessing that same data after the first read in order to make it adequate for our data model. Since the data is being stored in RAM after each query during the script execution, we can use that RAM-stored data instead of going directly to the original data source. This is accomplished via a Resident load.
The keyword Resident
can be likened to the keyword From
in a query. The difference is that the Resident
keyword is used to reference the data in RAM model, that is, all the tables that have been previously read in the preceding queries of the same script. The process for achieving this is as follows:
SalesData: LOAD InvoiceNumber, Date, SalesPerson, Department, Amount as InvoiceAmount; SQL SELECT * FROM DataBaseName.dbo.Sales;
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.