Quick searching data in a table
You can enter one or more search terms in the Filter text box at the top of the View tab to perform a quick search of the data in a table.
Search scope
All the source data in the table is searched, not just the data displayed in the current view. For information about source data, tables, and views, see The structure of Analytics tables.
Entire records are searched, including any undefined portion of the record, rather than specific fields. For example, entering casino finds records that contain “casino” anywhere in the record. You can subsequently modify the search if you need to search a specific field (character data searches only).
Computed fields and related fields are not searched unless you subsequently modify the search to specify the specific field.
Data types searched
Searching character data is the most straightforward use of quick search. You can also search numeric and datetime data, however there are some additional considerations to take into account, explained in subsequent sections.
Automatic conversion of search term to a filter
The search term or terms you enter are automatically converted to a global filter that uses the FIND( ) function.
For example, entering casino results in the filter FIND("casino").
The filter auto-populates the Filter text box, from where you can modify it, if required. For example, you could modify FIND("casino") to limit the search to a specific field: FIND("casino", Merchant).
The filter is also added to the filter history and to the command log, from where you can subsequently reapply it.
Search terms and filter syntax automatically distinguished
The Filter text box automatically distinguishes between search terms and filter syntax. For example, entering match in the Filter text box searches for the character string “match”, whereas entering match(City, "New York", "Washington") creates a filter using the MATCH( ) function.
Steps
Note
When searching for numbers or datetime values, you need to match the source data formatting rather than the formatting in the view. For more information, see Quick searching numeric or datetime data.
Search for one or more search terms
In the Filter text box at the top of the View tab, type one or more search terms and press Enter.
With multiple search terms, the quick search performs a logical OR operation and finds records that contain at least one of the search terms.
Search for an exact phrase
In the Filter text box at the top of the View tab, type the phrase, enclose it in double quotation marks, and press Enter.
The quick search finds only those records that contain the exact phrase.
Limit the search to a specific field (character fields only)
- Modify the automatically generated filter
in the Filter text box by typing a comma after the search term,
and then add the name of the field.
For example, modify FIND("casino") to FIND("casino", Merchant).
- Press Enter.
The search is restricted to the field that you specified.
Note
You must use the physical name of the field, which may not be the same as the display name of the field in the view.
To check the physical name, right-click the appropriate column header and select Properties. If necessary, copy the physical name from the text box at the top of the Modify Column dialog box. Do not use the Alternate Column Title.
To search in a related field you must specify the fully qualified name of the field (that is, table.field name). For example: FIND("casino", Vendor.Vendor_Name)
Quick searching character data
When quick searching character data, you can enter whole or partial words, or exact phrases.
- If you enter more than one word, the quick search performs a logical OR operation and finds records that contain at least one of the words.
- If you want to search for an exact phrase, enclose the phrase in double quotation marks.
- To isolate a
search term, include a trailing space after the term and enclose
the term and the space in double quotation marks.
For example, "cash " returns “cash” but not “cashier”, assuming that in the data the string “cash” is followed by at least one space
Search term |
Returns records that contain: |
---|---|
cas |
|
casino |
|
casino liquor |
|
“Diamond Casino” |
|
“Diamond Casino” “Golden Casino” |
|
casino, “ABC Liquors” |
|
“ABC L” |
|
“cash ” (the word ‘cash’ followed by one space) |
|
Quick searching numeric or datetime data
Note
If you want to search for numeric or datetime data in a specific field, use quick filtering. For more information, see Quick filtering data in a view.
When quick searching numeric or datetime data, you need to remember that you are searching the underlying source data rather than the data displayed in a view.
Numbers, dates, and times are often formatted differently in the source data than they are in the view. Search terms need to match the source data formatting rather than the formatting in the view.
You can select Edit > Table Layout to view the source data for a table.
Quick searching numeric data
The numeric format in the source data affects which records are returned for a specific search term.
Search term |
Numeric format in view |
Numeric format in source data |
Returns records that contain: |
---|---|---|---|
1234.00 |
9999.99 |
9999.99 |
1234.00 |
9,999.99 |
no records returned |
||
1,234.00 |
9,999.99 |
9999.99 |
no records returned |
9,999.99 |
1,234.00 |
||
9.999,99 |
no records returned |
||
(1234.00) |
(9999.99) |
(9999.99) |
(1234.00) |
-9999.99 |
no records returned |
||
1234.01 |
9999.99 (number rounded) for example: 1234.01 |
9999.9999 for example: 1234.0085 |
no records returned |
1234.0085 |
1234.0085 |
||
123 456 |
9999.99 |
9999.99 |
|
Quick searching datetime data
The datetime format in the source data affects which records are returned for a specific search term.
Search term |
Datetime format in view |
Datetime format in source data |
Returns records that contain: |
---|---|---|---|
12/31/2015 |
MM/DD/YYYY |
MM/DD/YYYY |
12/31/2015 |
DD/MM/YYYY |
no records returned |
||
YYYYMMDD |
no records returned |
||
31/12/2015 |
MM/DD/YYYY |
no records returned |
|
DD/MM/YYYY |
31/12/2015 |
||
YYYYMMDD |
no records returned |
||
20151231 |
MM/DD/YYYY |
no records returned |
|
DD/MM/YYYY |
no records returned |
||
YYYYMMDD |
20151231 |
||
2015-12-31 |
YYYY-MM-DD |
YYYY-MM-DD |
error message no records returned |
FIND("2015-12-31") |
2015-12-31 |
||
23:59:59 |
hh:mm:ss |
hh:mm:ss |
23:59:59 |
hhmmss |
no records returned |
||
20151231.235959 |
MM/DD/YYYY hh:mm:ss |
YYYYMMDD.hhmmss |
20151231.235959 |
MM/DD/YYYY hh:mm:ss |
no records returned |
Additional characteristics of quick searching
Quick searching has these additional characteristics:
Characteristic | Description |
---|---|
Case-sensitivity | The search is not case-sensitive. |
Wildcards | Wildcard characters in search terms are not supported. |
Spaces | Leading, trailing, and intervening spaces in search terms are considered only if you enclose the search term or terms and the spaces inside double quotation marks. When spaces are enclosed by double quotation marks they are treated like characters and must be exactly matched in the data. |
Quotation marks | Only double quotation marks can be used for enclosing phrases. Single quotation marks are not supported for this purpose and are treated like a regular character. |
Computed fields | Computed fields are not searched. |
Related fields | Related fields are not searched. |
Limiting search by field | When modifying the auto-populated filter to limit the search to a specific field you can specify only character fields. Specifying a numeric or datetime field causes an error. |
Unsupported characters |
If used in quick search terms, the following characters may give inconsistent results, or cause an error message, because they are the operators used in Analytics expressions: ^ * ( ) - + = < > If you want to search for one of these characters, manually enter the FIND( ) function in the Filter text box. For example: FIND("a+b") or FIND("2015-12-31") |
Field boundaries Trailing spaces |
Field boundaries in records are ignored, which means it is possible for a search term to match a string of characters across a field boundary. Trailing spaces in fields are treated like characters. Search results found across field boundaries may not be valid results, unless you specifically intend this type of search. For example, the last digits of an account number and the first digits of an amount in an adjacent field could match a numeric search term, but would probably be a false positive. Note The field boundaries under discussion are the ones that appear in the table layout, based on the physical order of fields in the layout. The order of fields in the layout can differ from the order of columns in the associated view, creating different adjacencies in the layout and the view. If it is unclear why quick searching is returning a particular record, select Edit > Table Layout to view the source data being searched. |
Additional searching and filtering information
- For many other data searching options, including using wildcards, see Searching data.
- For more information about filtering, see Filtering data.
- For more information about the FIND( ) function, see FIND( ) function.