Using the Qlik data eXchange (QVX) Writer

Introduction

Qlik is a business intelligence tool that integrates data and creates visualizations to aid in data analytics. In FME 2018.0 a new Qlik writer was introduced that can write to Qlik data eXchange (QVX) files. The QVX format is XML based and is optimized for importing and exporting data for use within Qlik. The file contains metadata about a single table as well as the actual data. This tutorial will demonstrate how to prepare your data for use with the QVX writer.

Requirements

Qlik Sense Cloud or QlikView

FME Desktop 2018.0+

Step-by-step Instructions

Our goal is to create a Qlik dashboard to analyze business licenses in a city. We will use FME to clean up the data, ensure that each business license has a neighborhood associated with it, convert the data types into a format Qlik can read and finally write out to the QVX format.

Note: This tutorial does not cover how to create a Qlik dashboard. Please see the QlikView or Qlik Sense Cloud tutorials offered by Qlik if you would like to learn how.

1. Open the template workspace

Open the QlikWriter.fmwt template workspace in FME Desktop 2018.0+. This workspace reads a CSV file containing business license data which is missing a neighborhood name for 5,912 records. A PointOnAreaOverlayer transformer overlays the Business Licenses on a Neighborhood Boundaries Shapefile to get the neighborhood name. Then a SubstringExtractor transformer obtains the remaining neighborhood names from the attribute LocalArea. Finally, an AttributeValueMapper remaps the attribute BusinessType into broader topics which will help clean up the analytics in Qlik.

Note: If you are working through this tutorial using your own data, you will need to reproject your data if it is not in an LL84 coordinate system and you would like Qlik to read your data as spatial.

Initial template workspace reading in Business Licenses and Neighborhood Boundaries.

2. Convert the Date and Time

The QVX Writer can only accept date and time values that are in FME format:

• %Y%m%d (FME Date) -> 20180417
• %H%M%S (FME TIme) -> 091910.9617319
• %Y%m%d%H%M%S (FME DateTime) - > 20180417091910.9617319
• ... or FME DateTime with offsets

We will need to convert two of our attributes, IssueDate and ExpiredDate. Since they are both in the same format we can use the same DateTimeConverter. Add a DateTimeConverter transformer to the canvas and connect it to the Output port on the AttributeValueMapper. In the parameters, set the Datetime Attributes to IssueDate and ExpiredDate. Then set the Input format to:

%Y-%N-%d %H:%M:%S

Then select FME Date as the Ouput Format, we don’t need time for this example, and then click OK.

DateTimeConverter parameters for IssuedDate and ExpiredDate

3. Write to Qlik data eXchange format

Now that the data is prepped we can write to the QVX format. Add a Qlik data eXchange writer, selecting Feature Type Definition as Automatic. For Feature Type Name, enter BusinessLicenses then connect it to both the Output and <Rejected> ports of the DateTimeConverter. The reason we are connecting it to both ports is that there are some <null> or empty values for IssueDate and ExpiredDate that weren’t converted so they were sent to the <Rejected> port. We still want those records though so make sure both output ports are connected QVX Writer.

In the writer feature type parameters, switch to the User Attributes tab and change the Attribute Definition to Manual. Please set IssueDate and ExpiredDate to the type date. If you have both date and time in an attribute, you would use the datetime type.

Now make sure that both Latitude and Longitude are set to the type real_64, this is the only date type that Qlik will accept for coordinate values.

Finally, change NumberOfEmployees and FeePaid to integer_32. By doing so, Qlik will treat these values as numerical and can be put into equations.

QVX Writer User Attributes parameters, ensure the Type is set correctly

4. Run the workspace and add the data to Qlik.

Run the workspace and then add the output BusinessLicenses.qvx file to either Qlik Sense Cloud or QlikView to create your dashboard.

Output table in Qlik Sense Cloud

A possible final dashboard that you can create using Qlik Sense Cloud or QlikView, this one is made using Qlik Sense Cloud

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.