WHERE Clause Syntax for Importing Data into Sentinel Visualizer

Import Source Data TypesIntroduction

Sentinel Visualizer lets you choose data to import from a variety of sources. The data can be from these formats:

  • Excel spreadsheet
  • Access database's table or query
  • SQL Server database's table or query
  • Text file

After choosing the source data, a screen with options for the data type appears with a preview of the data.

Import Excel FormImport Excel Options

At the bottom of the form is an optional WHERE clause:

WHERE clause

By default, the WHERE clause is blank and all the records are imported. You can use the WHERE clause to select a subset of the source data rows based on values in the columns.


A relationship is defined with two entities and their relationship type. If you have call data records containing two phone numbers and the first always calls the second, you do not need to use a WHERE clause. Simply assign the first phone number to Entity 1 of the relationship and the second to Entity 2.

But if your data has two phone numbers and a column designating whether the call was outgoing or incoming, you need to import the data differently to preserve the call direction:

Phone 1 Phone 2 Direction
703-356-4700 202-555-1212 OUT
703-356-4700 212-555-3434 IN

We need to swap the phone number columns based on whether it is an outgoing or incoming call. Rather than two different Source Data, use the WHERE clause:

  • Set the WHERE clause to Direction = 'OUT' to import Phone 1 and 2 assigned to Entity 1 and 2 respectively,
  • Create another import with Direction = 'IN' to assign Phone 2 to Entity 1, and Phone 1 to Entity 2. After both imports, your relationships have all the calls with the correct direction from Entity 1 to Entity 2.

WHERE Syntax

The WHERE clause uses standard SQL syntax. Here are some hints:

  • Put the column name in brackets: [Column Name]
  • For an exact match, use the equal sign = followed by the value.
Data Type Syntax Example
Text (string) Use single quotes before and after the value. Note the value is not case sensitive. [Product Name] = 'Sentinel Visualizer'
Number No quotes are needed and just use the number [Quantity] = 100
Date For Excel, Access and Text, put the value between hash tags # with Year-Month-Day syntax (YYYY-MM-DD): [Order Date] = #2023-08-31#
For SQL Server, use single quotes around the date. [Order Date] = '2023-12-31'

Range Syntax

Instead of an exact match with the equal sign, you can specify range searches on numeric and date columns:

Operator Selection Objective Example
<> Does not equal Not in Virginia [State] <> 'VA'
>= Greater than or equal to 100 and more [Quantity] >= 100
> Greater than More than 100, but not 100 [Quantity] > 100
< Less than Less than 100, but not 100 [Quantity] < 100
<= Less than or equal to 100 or less [Quantity] <= 100
BETWEEN .. AND Select values between low and high values.
(Inclusive of those values similar to >= AND <=)
May 2023 records [Start Date] BETWEEN #2023-05-01# AND #2023-06-01#

Use Range Search on Date Columns

Note that exact matches on Dates are challenging. Date fields may contain both date and time. Midnight is considered 0 time; hours after that are fractions of a day (e.g. Noon is 0.5 day).

Date fields that have time may only appear as dates without time based on the fields formatting, so you may not realize the data contains time.

When filtering on values for a day, it's best to use the range for a day like this: [Start Date] BETWEEN #2023-05-21# AND #2023-05-22#

That actually gets both midnights, so if that's an issue, use syntax like this: ([Start Date] >= #2023-05-21#) AND ([Start Date] < #2023-05-22#)

Advanced WHERE Syntax

For more advanced searches:

Operator Selection Objective Example
AND AND to combine multiple criteria. Use parentheses around each selection. Outgoing calls after January 1, 2023 ([Direction] = 'OUT') AND ([Called Date] >= #2023-01-01#)
OR OR can be used to select a combination of values Records from Washington DC and Virginia ([State] = 'DC') OR ([State] = 'VA')

Select values without using multiple OR clauses. The values are comma separated within parenthesis.

The same rules apply to the values based on type:

  • Strings are between single quotes
  • Numbers do not need quotes
  • Dates use # (or single quote for SQL Server).
Records from Washington DC, Maryland, and Virginia

Values in a numeric field ID equal to 5, 25 or 72
[State] IN ('DC', 'MD', 'VA')

[ID] IN (5, 25, 72)
LIKE LIKE with % to perform wildcard searches Names starting with A [First Name] LIKE 'A%'
Wildcard can also be used to find strings that end with a letter Names ending in Z [First Name] LIKE '%Z'
Multiple wildcards can be used to find text in any part of the field Fields with ME in it [Last Name] LIKE '%ME%'
NOT IN Add NOT to perform the opposite. NOT IN would be rows without those values. Records outside of Washington DC, Maryland, and Virginia [State] NOT IN ('DC', 'MD', 'VA')
NOT LIKE NOT LIKE selects rows that do not match the pattern Last Names that don't start with Mc [Last Name] NOT LIKE 'MC%'

Microsoft Certified Partner

Microsoft Partner Network

Partners Welcome

Do you provide services to government and commercial customers seeking analytic solutions?
Contact us

In-Q-Tel Portfolio Company

InQTel Portfolio Company
Learn more