# Custom SQL Filter

CrossEngage's Predictions Platform provides various filtering options to add or remove Customers from certain Selections. This includes customer lists uploaded as files (blocklists and allowlists) or other Selections. These methods can be quite useful to restrict Selections, but can be time-intensive and limited in capability.

Hence the Platform allows you to enter a Custom SQL filter when creating Insights, Models or Prediction. For example, when creating a model, a Custom SQL Filter can be added in (8) to filter Target Variable, or in (10) to filter Target Group.

<figure><img src="/files/NOinDYbRA3ZFlRwrm2ik" alt=""><figcaption></figcaption></figure>

To use the SQL filter, write a **SQL condition** in the given field. The SQL Condition is the part of the query that can be written in a "WHERE" clause.

### Data Sources

* **Target Variable:** All fields contained in the transactional data
* **Target Group:** All features automatically created or custom-built by yourself or a data scientist.

### Data Types

The fields can have the following data types:

* **Numeric:** Whole numbers or floating point numbers
* **Strings:** Any character such as digits, letters, special characters, etc. Strings are written in 'single quotes'.
* **Date & Time**
* **Boolean:** True or False

### Operators

Operators can be used to compare or combine different values or expressions:

* **Arithmetic Operators:** These can be applied to numeric values: +, -, \*, /, etc.
* **Comparison Operators:** These compare values ​​between two expressions and return a boolean value: =, >, <, !=, IN, BETWEEN, LIKE etc.
* **Logical Operators:** Boolean operators, which can combine different expressions and values ​​together: AND, OR, etc.

{% hint style="warning" %}
Remember to use the correct case when using filters. A simple way to avoid case-sensitivity errors is to keep all values stored in the data tables in lowercase.
{% endhint %}

### Wildcards

Wildcards are placeholder characters, which can be added to a string when a certain part of string is unknown or not fixed. Wildcards are used with the SQL `LIKE` operator to find similar strings.

* **Underscore:** Underscore (\_) can be used to replace a single unknown character in a string.
* **Percent:** Percent (%) can be used to replace a series of unknown characters, of any length.

### Examples

#### Target Variables

To Filter blue cups and green plates:

```
(productgroup_id = ‘Cups’ AND color = ‘Blue’ ) OR (productgroup_id = 'Plates' AND color = ‘Green’)
```

To Filter LD1, LD2 and LD3 Printers:

```
productgroup_id2 IN (‘Laserdrucker LD1’,‘Laserdrucker LD2,‘Laserdrucker LD3’)
```

Alternately, we can use a Wildcard:

```
productgroup_id2 LIKE ‘Laserdrucker%’
```

#### Target Group

To Filter customers in France:

```
company_country_IN_t000 = 'FR'
```

To Filter customers with recency between 10 and 20 weeks:

```
FILTER_transaction_recency_IN_t000 BETWEEN 10 AND 20
```

To learn more about SQL, [Click Here](https://www.w3schools.com/sql/default.asp).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://documentation.crossengage.io/predictions-platform/overview/model-builder-tab/custom-sql-filter.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.