Databases Reference
In-Depth Information
As you can see, we have one field (column) for each month. We also have a
Department
field, with its corresponding field values in one single column.
The values in the data area of the table are amounts. Let's assume they are
Sales
amounts.
The problem with this matrix-like structure is that if, for example, we want to
obtain the total sales for each department, we would need to create an expression
like the following:
Sum (Jan) + Sum(Feb) + Sum(Mar) + Sum(Apr) + Sum(May) + Sum(Jun)
At the same time, we wouldn't be able to create a trend chart, because all months
are stored as different dimensions. So, we need to make it it our purposes.
For us to use this table better in a QlikView data model, we need to convert it to a
traditional table with the following structure:
Department
Month
Sales
A
Jan
160
A
Feb
336
A
Mar
545
A
Apr
152
A
May
437
A
Jun
1
This way, in our charts, we will be able to create expressions such as:
Sum(Sales)
Working with the Crosstable Wizard
Just as we did with our preceding example, we will load this file into a new QVW,
so let's begin by creating a new QlikView document and saving it as
Crosstable
example.qvw
. The new file should be saved in the
Airline Operations\Side
Examples\Chapter 9
folder.
After saving the file, make sure the
Crosstable example.xls
file is also at the
same location.
Next, open the
Script Editor
window and bring up the
File Wizard
by clicking on
the
Table Files…
button.