Filters
Query Filters are applied to each projection list (row) of your query results to determine if the row should be included in the final result set. The Filter must result in a Boolean (true, false) value.
Get started by clicking Add filter in the Concept Pop-up Menu.
LIMS s_sample Query and Results before adding a Filter. Returns 200 Rows.
Query Filter Builder
Start by choosing a Function in the list.
In this example, we will filter all values with a COLLECTIONDT less than October 1st, 2020. So, we choose the Less Than function.
See: Functions
Query Filter Builder: Searching for less and Selecting the Less Than Function.
Once the Less Than function is selected, you are prompted to add two arguments (arg1, arg2). For Less Than, the function checks that arg1 < arg2.
Query Filter Builder: Once the Less Than function is selected, you are prompted to add two arguments (arg1, arg2).
If you need help understanding the Function, hover over the ❔ icon, or read more about Functions.
Setting the First Argument
Selecting COLLECTIONDT as the first argument shows that it is a string value. It will be necessary to convert it to a date before comparing.
Query Filter Builder: Setting arg1 to COLLECTIONDT indicates it is a string value.
Converting a String to a Date
Change the first argument to XSD DATETIME.
Query Filter Builder: Using the xsd:dateTime function to cast a string to a date.
The xsd:dateTime function requirs a single argument. Select COLLECTIONDT from s_sample variables.
Query Filter Builder: Converting COLLECTIONDT from a string to a date using xsd:dateTime.
Setting the Second Argument
We want to filter results that are less than (before) October 1st, 2020. Currently, you'll need to enter the date in ISO-8601 date format (YYYY-MM-DDTHH:MM:SS), click Add as Constant, then set the data type to dateTime.
Query Filter Builder: Setting the second argument to a constant date (October 1, 2020) in ISO-8601 format.
Query Filter Builder: Setting the data type of the constant to dateTime.
Running the Query
Once the filter has been saved, it can be seen under the Query.
Click on Run to execute the query and review the results.
Query Filter Builder: The Filter lt(xsd:dateTime(s_sample.COLLECTIONDT), 2020-10-01T00:00:00) is shown under the Query. Running the filtered query returns 94 rows.
Click on the filter text to open the Query Filter Builder.
Click on the red trashcan to remove the Filter.
Check back later for more details about Query Filters.