Sentinel Visualizer lets you choose data to import from a variety of sources. The data can be from these formats:
After choosing the source data, a screen with options for the data type appears with a preview of the data.
Import Excel Options
At the bottom of the form is an optional 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|
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:
The WHERE clause uses standard SQL syntax. Here are some hints:
|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'|
Instead of an exact match with the equal sign, you can specify range searches on numeric and date columns:
|<>||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#|
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#)
For more advanced searches:
|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:
|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%'|