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:

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.

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.

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:

OperationDescription
EQevaluates if two values are equal
NEQevaluates if two values are not equal, doesn't include null values
GTevaluates if first value is greater than second value
GTEevaluates if first value is greater than second value or equal
LTevaluates if first value is lower than second value
LTEevaluates if first value is lower than second value or equal
INevaluates 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_NULLevaluates if first value is NULL
NULLevaluates 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


FunctionDescription
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.

Next Steps