It is recommended to get familiar with filter function before proceeding with this tutorial.
You can set up filters for view in Filter dialog.
Sometimes it is more convenient to add filter directly to indicator formula. You can do this when:
- you are cross referencing data from other data sets.
- you do not want your users to change filters.
Filtering Attributes
The filter function supports most of the operators supported by PostgreSQL such as in/not in, ilike, like/not Like, similar to/not similar to etc. "ilike" is similar to "like" but case insensitive to the pattern matched.
- Filter using operator: in/not In
Following example filters data to include only Paris, Berlin and London in their City attribute. Result returns aggregation of visits in these three citites.
filter("L_CITY in ('Paris','Berlin','London')") { return M_VISITS; }
Note the correct usage of apostrophs in the example.
- Regular Expressions
Pattern | Description |
---|---|
_ | Stands for any single character. |
% | Stands for any sequence of zero or more characters. |
* | Denotes repetition of the previous item zero or more times. |
+ | Denotes repetition of the previous item one or more times. |
? | Denotes repetition of the previous item zero or one time. |
| | Denotes alternation (either of two alternatives). |
Some examples for regular expressions: 'abc' LIKE 'abc' true 'abc' LIKE 'a%' true 'abc' LIKE '_b_' true 'abc' LIKE 'c' false
Filtering Indicators
Following example counts number of records which have Indicator's value of visits greater than 3.
count = 0 filter('M_VISITS > 3') { count++; } return count;
Note that filter is applied on every record and not on its aggregated value displayed in the view.
Date filtering
When filtering dates, the only allowed format is 'yyyy-MM-dd'. Following example returns number of students who had an exam on 12th September 2014.
filter("L_DATE in ('2014-09-12')") {L_STUDENT_COUNT}
For filtering whole month or year, put date-part function inside the filter. Following example return number of students who had an exam in September 2014.
filter("date_part('month', L_DATE) = 9 AND date_part('year', L_DATE) = 2014") {L_STUDENT_COUNT}
Time filtering
When filtering time, the only allowed format is 'HH:mm:ss'. Following example returns number of patients who arrived at 10:00
filter("L_TIME in ('10:00:00')") {L_PATIENT_COUNT}
For filtering whole hours, minutes or seconds, date_part function can be put inside the filter functiion. Following example returns number of patients who arrived between 10:00 and 10:59.
filter("date_part('hour', L_TIME) =10") {L_PATIENT_COUNT}
Multiple Filters
Following example returns number of students who had Exceptional results from Math subject.
filter("(L_PASS_RESULT in ('Exceptional')) AND (L_PASS_SUBJECT in ('Math'))"){ L_STUDENT_COUNT }
You can combine multiple filter conditions with AND or OR conjunction.
Code Alternatives
This feature is available since BellaDati 2.9.2
It is possible to write filters in formulas to different ways. First one is mentioned in examples above. The second one uses more programmatic approach. In few steps, the filter is created and then built.
def f = createFilter() f = andFilter(f, 'L_ATTRIBUTE', 'OperationType', 'value1') filter(buildFilter(f)) { ... }
Function andFilter() is used to add another condition to the filter. It is possible to add more than condition, by using multiple andFilter() functions.
Following operations are available:
Operation | Description |
---|---|
EQ | evaluates if two values are equal |
NEQ | evaluates if two values are not equal, doesn't include null values |
GT | evaluates if first value is greater than second value |
GTE | evaluates if first value is greater than second value or equal |
LT | evaluates if first value is lower than second value |
LTE | evaluates if first value is lower than second value or equal |
IN | evaluates if first value equals one of the values from the list |
NOT_IN | evaluates if first value does not equal any of the values from the list, null values are included as well as null doesn't equal any value in the list |
NOT_NULL | evaluates if first value is NULL |
NULL | evaluates if first value is not NULL |
For operations NULL and NOT NULL, the second value is empty.
def f = createFilter() f = andFilter(f, 'L_ATTRIBUTE', 'NULL','') filter(buildFilter(f)) { ... }
For operations IN and NOT_IN, it is possible to define more than one value for comparison.
def f = createFilter() f = andFilter(f, 'L_ATTRIBUTE', 'IN', 'value1', 'value2') filter(buildFilter(f)) { ... }
Filtering GeoJSON Values
This feature is available since BellaDati 2.9.2
Filters can be also used to evaluate whether a certain GEO point is located inside an area defined by o GeoJSON. To be able to use this function, PostGIS has to be installed on the server. Administrators can open Database Summary page to see whether PostGIS is installed or not.
String f = filterGeoJSONContains('L_GEOJSON', '134.256028,-24.162618') filter(f) { L_NAME_COUNT }
Another option is to use this filter to retrieve the name of a county or state where the GEO point is located.
String f = filterGeoJSONContains('L_JSON', '134.256028,-24.162618') filter(f) { crossValue('GEOJSON', 'L_NAME_FIRSTVALUE') }
Filtering in ML studio and Endpoints
Function | Description |
---|---|
andFilter(Filter first, Filter second) | Creates an AND filter concatenating multiple filters. |
orFilter(Filter first, Filter second) | Creates an OR filter concatenating multiple filters. |
isGreaterFilter(String column, Object value) | Creates a filter which checks whether column value is greater than the specified value. |
isGreaterOrEqualFilter(String column, Object value) | Creates a filter which checks whether column value is greater than or equal to the specified value. |
isEqualFilter(String column, Object value) | Creates a filter which checks whether column value is equal to the specified value. |
isNotEqualFilter(String column, Object value) | Creates a filter which checks whether column value is not equal to the specified value. |
isLessFilter(String column, Object value) | Creates a filter which checks whether column value is less than the specified value. |
isLessOrEqualFilter(String column, Object value) | Creates a filter which checks whether column value is less than or equal to the specified value. |
isInFilter(String column, Object value) | Creates a filter which checks whether column value is in a collection of values. |
isNotInFilter(String column, Object value) | Creates a filter which checks whether column value is not in a collection of values. |
isNullFilter(String column) | Creates a filter which checks whether column value has a {@code null} value. |
isNotNullFilter(String column) | Creates a filter which checks whether column value does not have a {@code null} value. |
likeFilter(String column, Object value, String matchType) | Creates a filter which checks whether column value like value. matchType possible values are: EXACT, START, END, ANYWHERE |