How to Collect Data using QlikView Plug-in

This article will show you how to create an Element (Report in this case) using a QlikView plug-in as a Data Source. It assumes that you have already established connectivity to your QlikView server.

1. Access New > Metric

Access New > Metric

Provide the basic information required for creating a new metric:

  1. Define this Metric's Measure. If you do not see the measure that you want to use, you can create one directly from the bottom of this drop-down list
  2. Select the Measurement Interval that applies to your element
  3. Give the element a unique Name
  4. Optionally, assign a Category.
  5. Click Next: define details to proceed with defining Data Collection details

NOTE:  To create a dimensioned Metric, you first need to create a Dimension sourced from the same data source.

2. Configure Data Collection

  1. Select the QlikView plugin serving as a Data Source for this Metric
  2. Set the Data Collection Trigger which is going to initiate updating information in this Metric
  3. Select a QlikView Object that should become a basis for this particular Metric
  4. Input Plugin Command manually (you may reference a table with parameters below) or use a Visual Editor

2.1. Example using the Visual Editor

 Example using the Visual Editor

The Qlikview Query Builder is called by Visual Editor link

  1. Select Fields and set Expressions
  2. You can pre-filter the information before fetching it into Metric Insights. To do so, add Filters in the Query Builder
  3. Optionally you can add 'ascending' and 'descending' Sorting to the field values

Save your settings. Command validation will start automatically.

Plug-in Commands

The query must include the columns, listed in the hint box to the right of the Plug-in command field:

  1. MI dimension (for dimensioned Metrics and Reports only);
  2. measurement datetime
  3. measurement value
Command Description Values Example
fields (dimensions) 'Fields' store the data that is used by Qlik View and can be compared to columns in a database table, containing one or more values. Qlik View divides data in two major types: dimensions and measures. (NOTE: Make sure not to confuse dimensions in Qlik View and Metric Insights Dimensions created for Reports and Metrics.) Dimensions in Qlik View are descriptive attributes (typically textual fields or discrete numbers), while measures are the fields that can be measured, aggregated, or used for mathematical operations. Dimension fields are usually used for row or column headings. fields = Channel, Year Month, Total Gross Profit
aggregates It is often required to look at numerical data (which is referred to in Qlik View as Measures) in an aggregated form (via mathimatical functions, such as summation, average, etc.) Aggregation functions perform a calculation on a set of values and return a single value. For example, if you have 3,000 sales transactions from 50 products in your data source, you might want to view the sum of sales for each product, so that you can decide which products have the highest revenue. sum(field), avg(field), count(field), count(*), min(field), max(field) aggregates = sum(profit), min(salary)
filter Using the 'filter' command you can exclude certain values or a range of values for a field. Several filering conditions can be combined by 'OR', 'AND'. NOTE: This filtering is performed by Metric Insights.
Each filtering condition consists of 3 values: field, corporate operator ( <, >, =< <=, etc.), value (numeric, date or ':last_measurement_time') filter = Year Month == '2016-04-11' AND Avg Sales Per Day > 1000000
external filter Filtering may be also performed externally by Qlik View. Qlik View handles external filters in the following format: ?(key)=(value) ?country = USA, UK
sort You can specify whether you want your field values to be sorted in the ascending or descenting order. ASC, DESC sort = Name ASC
var This command allows creating a new custom field that can be used in all commands listed above. var mon_salary = salary / 12
fields = Name, mon_salary

3. Collect Data for the Metric

If entering plug-in command manually:

  1. Validate the command. If your statement is valid, the statement box is green; if there are any errors, the box is colored in red and errors will be explained in the field below.
  2. Collect Data
  3. At the upper right corner of the screen click Enable and Publish to save the element and make it available to other users in the system

[Result] Metric will be displayed in viewer

Alternative Plug-in Commands

If you want to query using a specific Dimension value then you would

  1. Modify the Metric Dimension and
  2. Add the extra selection of the Dimension value using the format:
fields = <Value1>, <Value2>

Where FIELD is the field name in QlikView that has the selections of the Dimension values.

From the example above you add:

?Company = Facebook, Twitter

So that final plug-in command becomes:

fields = Company, Date, Number of tweets
?Company = Facebook, Twitter

Select Specific Dimension values using Select Listbox

An alternate approach to query using a specific Dimension value uses the following format:  

?select = <ListBoxID>, <Value1>, <Value2>

Where the ListBoxID is the control in QlikView that has the selections of the Dimension values.

From the example above, add:

?select = LB122, Facebook, Twitter

Final plug-in command becomes:

fields = Company, Date, Number of tweets
?select = LB122, Facebook, Twitter