Filter Datasource Field
- 26 Aug 2024
- 4 Minutes to read
- DarkLight
Filter Datasource Field
- Updated on 26 Aug 2024
- 4 Minutes to read
- DarkLight
Article summary
Did you find this summary helpful?
Thank you for your feedback
General
This type of action can be used to filter one or more values on a Datasource field.
Example: Suppose you have a datasource that contains data for many years and those years are contained in a field named Year. If you want to filter the model of the datasource to a specific year (or to specific years), you can use this action to accomplish that.
Properties
This type of action has the following properties:
Name | Description |
|---|---|
| Datasource Field | The Datasource field to be filtered. |
| Values | An list of one or more Expressions which represent values to filter the Datasource field to. |
| Remove Filter if List is Empty | This property only applies if the Expressions contained in the Values property evaluate to an empty list. If you disable this option, this situation will lead to an exception; if you enable this option, an empty list will lead to any filter of the Datasource field to be removed so that the field is unfiltered. |
| Perform Match Filter | If enabled Mail & Deploy performs a match filter rather than a direct filter (see below). |
| Invert Filter | If enabled, the Datasource field will be filtered according to the other settings of the action and as a last step the filter will be inverted, which means that all values that were excluded by the filter will be included and all values that have been included in the filter will be excluded. |
| Is Optional | Under normal circumstances an exception will be raised if a filter cannot be set by the action (e.g. if you try to filter to a value that does not exist in the Datasource field to be filtered); when enabling this option, the filter is considered to be optional, which means that no exception will be raised in such a situation and the field may remain unfiltered if that is the case. |
Restrictions
Some Datasource fields have filter restrictions on them.
Example: If the field is part of a QlikView Datasource and has the option Always One Selected Value enabled, that option restricts filtering on that field.
Mail & Deploy cannot perform filter operations on such fields, but a Remove Datasource Field Filter Restrictions Action can be used to remove those restrictions.
Filter Types
Direct Filters
A direct filter will be applied if the Perform Match Filter option is disabled and generally means that the values provided in the Values property need to exactly equal to values of the Datasource field to be filtered.
Example: If the datasource contains a field named Year and that field has three values (e.g. 2016, 2017, and 2018), a direct filter will only work if the Expressions of the Values property return 2016, 2017, 2018 or a combination thereof. If one of the expressions returns 2015, an exception will be raised, because value 2015 cannot be filtered to, unless Is Optional is enabled.
Also, when using a direct filter, datatypes of the individual values matter.
Example: A datasource has a field named Year which contains the numerical values 2016, 2017, and 2018. If any of the Expressions of the Values property of the action returns the string (text value) David, the filter will fail with an exception, because not only is David not a valid value of the field to be filtered, but it is a text value and the field only contains numeric values.
Match Filters
A match filter will be applied if the Perform Match Filter option is enabled. In that case Mail & Deploy will not filter the exact values provided by the Values property of the action but instead use the wildcard filter functionality of the Datasource in which a field is filtered. Every value of the Values property will be interpreted as string (text value) and needs to be a valid wildcard filter expression for the associated type of Datasource (see below).
Example: Suppose you want to filter a field named Sales Rep Name in a QlikView or Qlik Sense datasource. When Perform Match Filter is enabled, you can specify the value David* to filter all values starting with David.
Date values can in some situations be much easier filtered by a match filter, because when using a Direct Filter (see above), the datatype of the values provided in the Values property of the action need to be the same datatype as the values of the filtered Datasource field. In some datasources (e.g. QlikView and Qlik Sense), dates are not stored as a well-formatted, readable text value (such as 30.12.2020 or 12/30/2020) but rather as numeric values which are hard to convert to. By using a match filter, you can provide a wildcard filter string that will then filter the textual representation of the date values of the field rather than the numeric values.
Example: Suppose there is field named OrderDate in a QlikView or Qlik Sense datasource which contains all order dates and you want to filter this field to 01.01.2018 (January 1st 2018). That date is stored in QlikView and Qlik Sense as the number 43101; theoretically you could create a Direct Filter and supply the numeric value 43101. However, there is an easier way: perform a Match Filter and supply the string 01.01.2018 in the Values property of the action. The match filter will search the textual representation of the date rather than its underlying numeric value. However, you have to format the wildcard filter string in such a way that it matches the date format of the field in the datasource.
The following table provides links to the documentation of wildcard searches for all types of Datasource that support them:
Datasource Type | Documentation Link |
|---|---|
| QlikView | QlikView Documentation about wildcard searches |
| Qlik Sense | Qlik Sense Documentation about wildcard searches |
Was this article helpful?