FilterData
The FilterData() function filters a specified range of data based on given criteria and returns the matching results.
Syntax:
Qx.FilterData(Range, Criteria, Columns)- In Excel with Quintessence addin versions prior to 25.1, use:
FilterData() - In the Quintessence Editor (Studio) using Quintessence language, use:
FilterData() - In the Quintessence Editor (Studio) using Python language, use:
Qx.FilterData()
ⓘ See parameter descriptions
| Range | The range of cells to transform. |
| Criteria | The mathematical expression that defines the filter to apply. Columns are referenced as C1, C2, C3, etc. Allowed expressions follow the Quintessence language syntax. Logical Operators: • Use & for AND• Use | for ORRow Position Filtering: You can filter rows based on their position using Line. For example Line <= 20. This filters only the first 20 rows. |
| Columns (optional) | Specifies which columns to include in the result. For example C1,C2,C3. If the value is set to *, all columns are returned. |
Remarks:
- Unless changed by an administrator, the default parameters are:
- Columns:
*
- Columns:
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Return equities that meet a criterion
Source data (range in Excel: A1:B3):
| Stock 1 | 100 |
| Stock 2 | -60 |
| Stock 3 | 75 |
Find all stocks with a value greater than or equal to 0:
=Qx.FilterData(A1:B3, "C2 >= 0", "C1")Result:
| Stock 1 |
| Stock 3 |
> Example 2: Return equities that meet multiple criteria
Source data (range in Excel: A1:B5):
| Stock 1 | 100 |
| Stock 2 | -60 |
| Stock 3 | 75 |
| Stock 4 | 50 |
| Stock 5 | -10 |
Find all stocks that has a value between (excluding) -70 and 70. Either of the following formulas can be used to achieve this:
=Qx.FilterData(A1:B5, "-70 < C2 & C2 < 70", "C1")=Qx.FilterData(A1:B5, "abs(C2) < 70", "C1")Result:
| Stock 2 |
| Stock 4 |
| Stock 5 |