A workspace is a collection of related projects. As with most things in this regard, there are no hard-and-fast rules about how many workspaces you need to create. Some organizations may have one. Others have one for every project. The norm is somewhere in between the two. We will probably have a workspace for related areas of ETL—perhaps by line-of-business or by data source.

There are two types of workspaces—standalone and repository. A standalone workspace will be stored locally on the developer's workstation. The repository workstation is stored in the Expressor repository. A standalone workspace can be converted to a repository workspace.

When we first open QlikView Expressor, we are presented with some options for workspaces:

When we select the New Workspace… option, we are presented with the following screen:

We can pick that our new workspace is either Standalone Workspace or Repository Workspace. If we select Standalone Workspace, we can specify the path to where the workspace will be stored. If we select Repository Workspace, we will give the connection information to where the repository is stored.

The repository is an Expressor implementation of the Subversion versioning system. This will be available with an Expressor server and is useful for multideveloper environments.

In QlikView Expressor, extensions are code libraries that allow Expressor to read and write to different file types and databases. There are some extensions that are installed out-of-box (such as the QlikView Extension), but we need to make sure that they are enabled. We need to access the Manage Extensions… option from the Desktop menu:

When we select the menu option, the Manage Extensions dialog opens:

Within this window, we can use the Current Workspace Settings tab to enable or disable extensions. We can use the Installs tab to install a new extension or uninstall an existing one.

Basically, a library and a project are the same thing. Both of them are storage locations for other objects, such as data flows, connection information, and so forth. The only difference is that a library cannot contain a deployment package—only a project can produce one of these packages for the integration engine.

A library is used to hold objects, or artifacts as they are called in Expressor, which will be shared among other projects.

To add a new project or library, you can select the Project or Library buttons on the ribbon bar or you can right-click on the workspace name:

To add either, we just need fill in a name and description in the dialog and click on the Create button:

When we create the project, we will see several folders—one for each of the types of artifact that may make up our project:

The different artifacts are as follows:

Configuring a File connection is quite straightforward. We just need to know the path to the folder:

After clicking on the Next button, we can enter a name (remember, no spaces) for the connection and a description:

We continue adding File connections for every folder that we are going to read from or write to.

If we are going to have many projects reading from or writing to the same set of folders, the connections should be configured in a library.

The connection to a database is fairly straightforward. Expressor comes with drivers installed for the following:

In addition, Expressor will natively support (can use its own property dialogs to configure the connection) the following drivers if they are installed from the vendor websites:

Finally, Expressor will also support other ODBC drivers, but a DSN will need to be configured outside of Expressor.

To add the database connection, we first need to select the correct driver to use:

We then fill in the database specific connection information (Expressor will test the connection before allowing you to continue) and then give the connection a name.

The QVX Connector Connection uses the same connector that we would use in QlikView to connect to an on-demand package. The only packages that can be used are those that will generate a QVX output.

There is a slightly different approach here in that we name the connection before we enter the connection details:

When we click on the Create button, Expressor will go ahead and save the connection and open the properties for us to edit.

We select the QlikViewExpressorConnector.exe option as the connector to use (you may also see the governance connector in the list, if you have the Governance Dashboard installed on the same machine). Click on the Actions button and select Build Connection String:

The following instructions are seen in the dialog box:

The instructions are as follows:

At its simplest, an Atomic type is simply a basic type such as string, date, number, and so forth. We can extend these basic types by adding constraints, such as length or value, to those basic types—implementing business rules.

We add a new Atomic type by right-clicking on the Types folder under the project and/or by clicking on Type in the ribbon bar:

We can now give a name to our Atomic type and Expressor will open the properties page for us to enter basic type and constraint information:

Depending on the base datatype, we can set different levels of constraint. If the constraint rule fails, we can set different corrective actions to be performed. The default is that the type should Escalate the breach of the constraint, which would normally throw an error in the dataflow.

Once we have set our constraints, we can save the Atomic type.

A Composite type is a collection of types that we can map our data onto. So, for example, we can create an order Composite type that represents exactly how we think an order should look. When we import external data, we can map that external data to our Composite type. By mapping everything to a Composite type, which can also encapsulate data constraints, we ensure consistency.

We create a Composite type by right-clicking on the Types folder under the project or by clicking on Type on the ribbon bar, as with the Atomic type. We name the artifact as usual and Expressor will open the properties window:

We can click on the Add button to add a new Atomic type to our Composite type:

For each attribute, we can assign a datatype and any constraints. We can also assign a Semantic Type (Atomic type) that we have previously configured (Shared) or create a new Atomic type (New (local) that will be added to the current project):

If we do assign a Semantic Type to the field, the constraint options will become grayed out because the constraints will be inherited from the Atomic type.

A schema represents the data that is being read or being written. We can have different types of schema for different types of data. For example, text data is handled differently than database data, which is handled differently from QlikView data. A schema can be configured from either the data itself—which is the usual way—or from a Composite type that we have already configured.

To configure a schema, we can either right-click the Schemas folder under the project or click on the Schema button in the ribbon bar:

A wizard will open that will allow us to configure the schema from the data. For file sources, we can either browse to the file or we can just paste some sample rows (browsing for the file will just load the first 10 rows):

We then specify the Field Delimiter value and the Record Delimiter value, any quote information, and the Encoding type. When we click on Next, the dialog will preview the data and show us the column headings. We can use the first row of the data for column headings by clicking on the Set All Names from Selected Row button:

After clicking on Next again, we can give a name to the schema (usual artifact name rules apply) and click on Finish to save the schema. Once we have saved the schema, we need to edit the details—we right-click on it and select Open:

We note that the schema has been assigned a generated Composite type (CompositeType1) and that the input fields are mapped to it. However, we want to assign our Composite type that we have already configured.

On clicking the Actions button to the right-hand side of CompositeType1, we can select to add a Shared type. When we select our Composite type, we will be prompted to generate a new mapping:

We would normally choose Yes to allow the system to make the mappings for us:

We do need to tweak a couple of the mappings. If you click on the link from OrderDate to OrderDate and then click on the pencil icon above it (or just double-click on the link), we can enter the correct format string for Expressor to interpret the text:

In this case, the data is in UK date format, so we need to specify DD/MM/CCYY, where D represents day, M represents month, C represents century, and Y represents year.

We should also edit the mapping for the sales price field because there is a dollar symbol. Expressor allows us to take care of that:

We can now select the CompositeType1 type from the drop-down menu and use the Actions button to delete it. Hit the Save button to save the schema.

This schema that we have just created can be used to either read or write text files. In fact, it is a good idea to design your schemas based on the required output rather than the inputs.

When creating a dataflow, we have a toolbox available of different operators that we can use within the dataflow. There are four categories:

Inputs

The Inputs toolbox contains eight options (depending on what extensions you have turned on), each used to read data:

This table describes each of the read operators:

Operator	Description
Read File	The Read Fil e operator will connect to a file using one of the file connections and one of the schemas that we have built
Read Table	The Read Table operator will connect to a table using a database connection and read the data
SQL Query	The SQL Query operator will execute a query that has been defined in a schema against a database connection
Read Custom	Read Custom allows you to run a data script to read and generate data that can be passed to another operator—this is an extremely powerful option
Read Lookup Table	This operator reads a lookup table that has been populated by another dataflow step
Read Excel	This is part of the Excel extension that allow us to read data from Excel files
Read QlikView	This QlikView operator is part of the QlikView extension and can read from QVW, QVD, and QVX
Read QVX Connector	The Read QVX Connector operator can read data from a QVX connector

Outputs

The Outputs toolbox contains nine operators (depending on what extensions you have turned on), each used to write data in different ways:

This table describes each of the write operators:

Operator	Description
Write File	The Write File operator will write data to a text file in a folder specified by a file connection.
Write Table	Write Table uses a database connection to write a database table. We can specify that a table should be created in the database if it does not exist.
Write Teradata PT	This allows you to write data to Teradata using Parallel Transporter. Note that you will need to download additional client libraries—TTU v13.10 or later.
Write Custom	Write Custom allows you to write data out using a data script. This is a powerful feature.
Write Lookup Table	This is used to populate an Expressor lookup table—not unlike a QlikView mapping table.
Write Parameters	This allows us to generate a parameters file that can be used to pass parameters to other options in the dataflow.
Trash	This is an interesting option—Trash takes an input and does nothing with it, it is as if you had thrown it away. It can be useful during development and troubleshooting.
Write Excel	This uses the Excel extension to create Excel output.
Write QlikView	This uses the QlikView extension to generate QVX output.

Transformers

Transformers are operators that allow us to transform data. As such, they will form a central part of almost any dataflow. There are six operators available:

This table describes each of the transformer operators:

Operator	Description
Aggregate	This operator allows you to perform grouping and aggregation on data
Join	The Join operator allows us to join—inner, left, right, and outer—data tables together
Transform	This is the core transformation operator, where we perform many of the applications of business rules
Multi-Transform	The multi operator will allow multiple transformations to be performed and up to nine different output streams
Pivot Row	This is similar to the CrossTable function in QlikView that takes data in columns and generates a new row for each column
Pivot Column	The Pivot Column operator is the opposite of Pivot Row—it takes multiple rows and creates one row with multiple columns

Utility

The Utility operators contain several operators that operate on data in ways that are not transformative, but are useful in an ETL system. There are six operators available:

This table describes each of the utility operators:

Operator	Description
Buffer	This is a useful operator where there may be issues with the timing of arrival of records to a multi-input operator (such as a Join). It will temporarily buffer data to disk until the next operator is ready to process it.
Copy	The Copy operator will take one input stream and allow us to split that into up to 10 output streams, each containing the same data.
Filter	The Filter operator allows us to create rules to filter data into multiple different output streams.
Sort	The Sort operator does what we expect: it sorts the data. We can assign a certain amount of memory for the operator to use as well as disk storage if it needs it.
Funnel	Funnel is similar to QlikView's Concatenate but more like SQL Union—it accepts multiple input streams and returns the union in one output stream.
Unique	The Unique operator will return one row for multiple values of a key field.

We add a new dataflow in a similar manner to other artifacts—right-click or use the ribbon. When the dataflow is first added, a blank workspace appears:

To the left-hand side, we have the operator panel. We can click-and-drag any operator from the panel onto the dataflow workspace:

On the right-hand side is a properties panel that allows us to set the properties for the currently selected object.

Configuring a Read File operator

After we have dragged an operator such as Read File onto the dataflow, we need to modify its properties:

The properties that you need to fill out are as follows:

Property	Description
Name	Free text name that we want to apply to the operator.
Connection	The file connection from which you want to read the file. All available connections will be in the dropdown.
Schema	The name of the schema that you will use.
Type	The Composite type that the schema will map to.
Mapping	The mapping set that will be used.
File name	The name of the file.
Quotes	Choose May have quotes or No quotes—depending on whether the file will have quotes or not.
Skip rows	If your text file has a header row, you will want to set this to 1.
Error handling	Either abort the dataflow, skip the record, reject the record, skip remaining records, or reject the remaining. Rejected records are put out the rejected records stream.
Show errors	Set whether the errors are shown or not.

Adding a Transformation operation

If we drag a Transformation operator from the Transformation panel onto the dataflow, we can then click on the output of the Read File operator and drag the mouse to the input of the Transformation operator:

The Read File operator should now change to green because it is complete and doesn't need us to do anything else. If it doesn't, we need to look at the messages panel to find out what we have forgotten!

If we click on the Edit Rules button on the Transformation operator's properties panel (or double-click on the Transformation operator), then the edit rules dialog opens:

The list of fields on the left-hand side is the list from the incoming stream. The list on the right-hand side is the list of fields that will be output. With nothing else changed, we see that the lists match. Every input field will be output. The white arrow to the left of each output field indicates that they are being output because they are input fields.

We can block an input field from being output by selecting that field in the left-hand side list and clicking on the Block Inputs button on the ribbon bar. If we do that for the LineValue field, the lists will look like this:

If we want to actually output a field that contains the line value, we can calculate it from the Quantity and SalesPrice fields. We need to first click on the Add button on the ribbon bar to add a new attribute to the Outputs list—for example, LineSalesAmount.

We then click on the New Rule button on the ribbon bar and select Expression Rule. We drag Quantity and SalesPrice onto the Add Input side of the rule and we drag our new LineSalesAmount field onto the Add Output side of the rule. In the expression area, we can replace nil with Input.Quantity*Input.SalesPrice:

Creating a QVX output

Once we have configured the Transformation operator, we can now drag a Write QlikView operator from the Outputs panel to dataflow and connect the output of the Transformation operator to the input of the Write QlikView operator.

Note

QVX is an open format to allow any developer to extract data into a file or data stream that QlikView and Qlik Sense can read. Like QVD files, QVX files only contain one table of data. At time of writing, the only Qlik format that Expressor can write is QVX.

We then set the properties of the output. Now, we haven't defined a schema for the output file, but Expressor has a nice facility where it can generate a new schema from the output of the previous operator. Clicking on the Actions button to the right-hand side of the Schema dropdown gives us a menu where we can select this option.

Once all the operators have been configured, they should all turn green. Now we can save it and test it. If all goes well, we should have a QVX file in the output folder.

Now that we have a working dataflow, we can package it up.

We simply add a new deployment package to the project and we can drag our dataflow into the Compiled Dataflows section.

That's it! The package can now be used in Expressor connectors and also with the integration engine on a schedule.