WHERE clause Syntax for Importing Data into Sentinel Visualizer

WHERE Clause Syntax for Importing Data into Sentinel Visualizer

Introduction

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 Form Import 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.

Example

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')
IN	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%'