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.If the first row contains headers, header notation may also be used. For example, if a column header is Price, reference it as [Price]. |
| 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.If the first row contains headers, header notation may also be used. For example, if a column header is Price, reference it as [Price]. |
Remarks:
- 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 |
> Example 3: Using header notation in parameters
Given the following range A1:C7 in Excel:
| Name | Age | Score |
| Alice | 25 | 88 |
| Bob | 30 | 72 |
| Charlie | 22 | 95 |
| Diana | 28 | 81 |
| Edward | 35 | 67 |
| Shelly | 22 | 55 |
Return the names of all candidates aged 25 or older who also have a score greater than 70.
=Qx.FilterData(A1:C7, "[Age] >= 25 && [Score] > 70", "[Name]")Result:
| Name |
| Alice |
| Bob |
| Diana |