Filter & Limit Data

• Setting up a Filter (Simple Criteria, Simple Query Builder)

  • Simple Comparators
  • Save or Use?
  • Date Fields

• Using Complex Criteria

  • JSON Comparators
  • created, modified & feeder_state

• Complex Filter Arguments for Copy Pasting (Collection)

---

By (view) filtering, the end user can easily extract valuable knowledge from a significant amount of data. By clicking Filter, a sub menu will appear showing available data filters. Please click on the downward arrow to expand and choose from available filters.

---

Setting up a Filter (Simple Criteria, Simple Query Builder)

• Please click on the New button right next to the filter sub menu.
• Assign a filter name and specify all fields (columns) that you would like to see listed.
• Then specify fields and desired values in criteria via the built-in query feature.
• If desired, combine multiple arguments via conjunction operators (AND, OR).

---

The example will filter all records with City == “München” (Munich):

Resulting view:

---

Simple Comparators

There are six simple comparators available:

• equal
• not equal
• less
• less or equal
• greater
• greater or equal

---

Date Fields

This feature is available as of Feeder v2.3 . While using the Simple Query Builder and declaring your conditions, you can type in a string instead of using the given datepicker as a kind of shortcut. There are certain rules to build the string:

• A minus [-] indicates a date in the past
• A number indicates the amount of time units
• Number 0 is equals to current
• You can use hours [h] , days [d] , weeks [w] , months [m] , years [y]
• A combination ( e.g -2w-2d ) is not possible

---

Examples

The following examples will use the date field Modified to demonstrate a few use cases. The affected dates will be marked red .

Query String : “Modified” greater -2d

Query String : “Modified” less or equal 1d

Query String : “Modified” not equal 0d

---

Save or Use?

Not all users do have a choice at this point:

Save

Is for administrators to define persistent filters that are stored in the data base and will be available to standard users too

Use

Is for standard users to define temporary filters that are not stored persistently in the database

Important

After using/ saving a filter, the filter view will be active. Please remember that the Quick Search box always refers to the active filter view! Read more here: Combination of Filters and Search Arguments

---

Using Complex Criteria

• Not always can the simple query builder tool cover all possible filter combinations.
• In this case, Feeder features the complex criteria input box where JSON syntax can be entered.
• The Complex Criteria option can be found as a hyper-link below the simple query builder.

The preceding filter sample would be “translated to”:

Important

When filtering, please use the key values as they are configured within the attribute definition and do not confuse these with the labels!

---

created, modified & feeder_state

There are a couple of system fields that are included in Feeder per default. They are not shown when opening a record, cannot be translated or set manually. But they contain valuable information for display in the standard view and can be queried:

created

Shows when a record was created (v1.13)

modified

Shows when a record was last modified. For example saving a new or editing an existing record, the value will be the moment that you clicked ‘Save’.

feeder_state

• 129, when an employee record is complete, this means that all required (mandatory) fields were filled.
• 32, when the employee record is an incomplete potential duplicate.
• 33, when the record is a complete potential duplicate. After validation the state will be 129.

Hint

If you would like to use these system fields in a custom scripts/calculated fields, please read more here Feeder System Fields

---

JSON Comparators

Important

These comparators are available when setting up filters or when Querying Export Data (Query). They will run against the Elasticsearch component.

Using the complex criteria input box, there are more comparators available (Feeder 2.0):

• "$eq" equal

  // returns all records where the first name is "Helena"
  {"FIRSTNAME":{"$eq":"Helena"}}
  

• "$ne" not equal

  // returns all records where the first name is not "Helena"
  {"FIRSTNAME":{"$ne":"Helena"}}
  

• "$lt" lower than, less

• "$lte" lower than or equal

• "$gt" greater than, greater

• "$gte" greater than or equal

• "$date" date

  • read more here: Filtering for Dates

• "$and" and-operator

• "$or" or-operator

  • read more here: Filtering by Combined Operators

• "$in" in

• "$nin" not in

• "$not" not

• $regex

  • allowing the search using regular expressions
  • can be used in filter settings only!

// fades the record where the lastname == Hel from the result view
{
  "LASTNAME": {
    "$regex": "[^Hel]"
  }
}

• "$like"

  • allowing the search of substrings or numbers
  • can be used in filter settings only!

// filtering all e-mail addresses of a specific domain
{
  "EMAIL": {
    "$like": "@pentos.com"
  }
}

---

Complex Filter Arguments for Copy Pasting (Collection)

Filtering for Dates

When filtering for a Date field, complex criteria have to be used. Besides, a String in ISO format (“YYYY-MM-DDTHH:mm:ss.sssZ”) is required, where “T” separates date from time and”Z” denotes the UTC time zone.

Important

Munich-based being in the CET timezone, deviation from UTC is 2 hours in summer and 1 hour in winter time. Consequently, these have to be considered by substraction of the current time value. Moreover it is advised to use the “greater than” ($gt) & “lower than” ($lt) operation here.

Search all user entries that where changed after 17:00 o’clock on September 22nd, 2014 (CEST).

{"CHANGEDATE": { "$lt": { "$date": "2014-09-22T15:00:00Z" } } }

Search all users who were hired before January 1st, 1977:

{"HIREDATE": { "$lt": { "$date": "1977-01-01T00:00:00Z" } } }

---

Filtering for Empty Attributes

Search all users with an empty country indication:

{"$or": [ { "COUNTRY": {"$exists": false} } , {"COUNTRY": ""} ] }

---

Filtering for Non Empty Attributes

Search all users that do have a manager indication.

{"$and": [ { "MANAGER": { "$exists": true }}, { "MANAGER": { "$ne": "" }} ]}

---

Filtering for Unequal

Often it is more helpful to filter the contrary, e.g. all records where a boolean attribute is false or has not been set explicitly instead of looking for boolean attribute equals true. This can be done by simple criteria and the according unequal operator, or by the following statement within complex criteria.

{"$or": [ { "TEST_DATA": false}, { "TEST_DATA": { "$exists": false }} ]}

(This case is taken from the Key Value Map (Checking Permitted Values & Marking) import script.)

---

Filtering by Combined Operators

Often a very specific population is needed, e.g. when you want to know all complete records where the country indication is Germany or Switzerland. This is how you could realize an adequate (complex) filtering argument:

{"$and":[{"feeder_state":129},{"$or":[{"COUNTRY":"Germany"},{"COUNTRY":"Switzerland"}]}]}

Hint

The readability of json code is greatly improved, if you copy paste the code and format it in your preferred editor!

---

Combined Filtering by One Attribute

Filtering by one attribute with different criterias can be realized with complex criteria only and would be done in the following way:

{"$and":[{"modified":{"$gt":{"$date":"2015-10-14T00:00:00Z"}}},{"modified":{"$lt":{"$date":"2015-10-16T00:00:00Z"}}}]}