CXAIR allows users to explore their data using ‘natural language’ queries as well as rapidly filtering results by clicking the on-screen values or generating different Filters. By making the process of querying simple, the user can retrieve the data they require without having to rely on running pre-canned reports or SQL queries.

To access the Query functionality, click Query at the top of the screen.

Interface

Searches are executed in specifically organised Search Engines. The available Search Engines are displayed in the Search within: drop-down list above the query bar. Upon selection, contained Indexes can then be specified. Restricting a search to a specific index allows more control over the results that are displayed.

Using the default settings, the number of results returned is displayed under the query bar, with links to the subsequent pages of results below. If Compact View is enabled in the User Preferences, the number of results are displayed above the query bar.

Navigate between different pages by clicking the specific page number or by clicking the arrows to display the next or previous pages of results. The number of results per page can be configured in the User Preferences.

Click the Hide Header arrows below the solution icon to hide the query bar and provide more screen space for the search results. Click the arrows again to reverse this action.

The Query Bar

To conduct a search, enter the required information into the query bar and click Search. Alternatively, press the Return button on the keyboard. Click Clear to remove the current query.

With the Enable Voice Searching option enabled in the User Preferences, searches can also be conducted via voice control by clicking the Microphone icon and speaking 'Search <search term>'. By speaking 'Load <report name>', reports can also be loaded using the voice control functionality.

When the Enable Report Searching is enabled in the User Preferences, it is also possible to load reports directly from the query bar by typing the report name and clicking a result.

Search Terms

There are a multitude of different search terms available to effectively navigate data:

Exact Match

To search for an exact match, simply type the required text in the query bar. The term must match a complete word within the value of any row or column and the results will highlight where the match was made in the record.

For example, searching for 'bread' would return 'Bread', 'Bread Mix', 'Wholemeal Bread' but not 'Breaded Chicken'.

Combining Search Terms

Combining terms provides a more focused set of results. Combining two or more separate words will return results that only contain every separate word entered.

Searching Fields

To search within a specific field, type the field name followed by a colon before entering a query.

This query is case sensitive and the spaces must be replaced with underscores (_). The required search term must be prefixed and suffixed with quotes (“”). The following format must be used: <field_name>:“<value>”.

Wild Card Searches

Wild card searches allow users to query data using fuzzy matching.

By replacing a single character with a ? and multiple characters with  * , the search will look to replace the wild card characters with a character in the search string. *  can only be used at the beginning or end of a search term.

This is especially useful when not all of the information is known to the user. For example, replacing the last three digits of a postcode with ??? will return results that match the exact string all other possible variations based on the wild card characters.

Fuzzy Searches

Typing the tilde (~) symbol at the end of a single word term allows users to perform a fuzzy search. This will return matches that are similar to the word typed.

To specify the level of similarity required, type a value between 0 and 1 after the search. A value closer to 0 will produce fuzzier results and if no value is typed, the default parameter, 0.5, is used.

Proximity Searches

This allows users to find words that are separated by a specific number of words in a document. Type the tilde (~) symbol at the end of a phrase followed by the number of words that separate the term.

Range Searches

Range searches allow users to match documents whose field values are between the lower and upper value specified by the search. Range searches can be inclusive or exclusive of the upper and lower bounds and results are sorted automatically.

Use square brackets ([]) to perform inclusive range searches and use curly brackets ({}) to perform exclusive range searches.

The following format must be used:

<field>:[“lowervalue” TO “uppervalue”] for inclusive range searches.

<field>:{“lowervalue” TO “uppervalue”} for exclusive range searches.

Brackets can also be mixed to provide an inclusive or exclusive search at the start or end of the range search.

Username Searches

Using the user.name syntax in conjunction with a corresponding username field, saved queries can dynamically filter the results based on the user who is running the query.

For example, with a field called user in the data, running +user:"{user.name}" will return a different result depending on who is running the report, allowing a single report to be written and shared across a wide user base.

Please note the CXAIR username must match the username present in the data being queried.

Grouping Syntax and Queries

Using parentheses (( )), it is possible to group clauses to form sub-queries. This is especially useful when controlling Boolean logic in a query. For example, grouping a query in brackets then using the AND operator would only return results that matched the sub-query and the additional string.

Effective use of parentheses to segregate search terms is paramount to ensuring the components are applied in the correct order. For example:

(-Field1:" " OR -Field2:" ") will produce very different results to (-Field1:" ") OR (-Field2:" "). This is due to how the underlying query engine combines search terms, with the second example displaying the correct syntax to combine the two separate entities.

Column Comparisons

To compare values between two columns, the following syntax is supported:

Advanced Date Queries

The Query functionality has the ability to parse date values using the system date, allowing users to specify an offset value from a particular field as well as other derived values such as TODAY, NOW, MONDAY etc. YESTERDAY can be derived by using the query TODAY-1, for example.

This functionality allows the creation of self-updating queries and reports. For example, creating a report that only displays information from the last seven days.

Date Parsing Syntax

The following syntax is available when performing advanced date queries:

Please note that using the SECONDS differential over thousands of records will increase the processing time.

When using the TODAY query, note that the current system date and a time of 00:00:00 is used, while using the NOW query would use the current system date and time.

Examples

If TODAY=Thu Jul 09 00:00:00 BST 2019
Then TODAY+1HOURS=Thu Jul 09 01:00:00 BST 2019

If NOW=Thu Jul 09 10:24:20 BST 2019
Then NOW+1WEEKS=Thu Jul 16 10:24:20 BST 2019

If MONDAY=Mon Jul 06 00:00:00 BST 2019
Then MONDAY+1DAYS=Tue Jul 07 00:00:00 BST 2019

If LAST_SECOND_OF_DAY=Thu Jul 09 23:59:59 BST 2019
Then LAST_SECOND_OF_DAY+1HOURS=Fri Jul 10 00:59:59 BST 2019

If FIRST_DAY_OF_MONTH=Wed Jul 01 00:00:00 BST 2019
Then FIRST_DAY_OF_MONTH+1WEEKS=Wed Jul 08 00:00:00 BST 2019

If LAST_DAY_OF_YEAR=Thu Dec 31 00:00:00 GMT 2019
Then LAST_DAY_OF_YEAR-12WEEKS=Thu Oct 08 00:00:00 BST 2019

If TRANSACTION DATE=Thu Nov 30 00:00:00 GMT 2019
Then MONDAY(Transaction_Date)-7DAYS=Thu Nov 23 00:00:00 GMT 2019

If TRANSACTION DATE=Thu Nov 30 00:00:00 GMT 2019
Then +Transaction_Date:["FIRST_DAY_OF_QUARTER" TO "LAST_DAY_OF_QUARTER"]
Will return all dates between 01/10/2019 and 31/12/2019

If TODAY=Wed Jul 10 2019 and the working day calendar in the Locale tab in Settings is set between Monday and Friday
Then TODAY-5WORKINGDAYS=Wed Jul 03 2019

If the current date is Mon Oct 25 2021
Then +Week_Commencing:"FIRST_DAY_OF_WEEK-1YEAR_SAME_WEEK"
Will return 19/10/2020, the first day of the same week in the previous year. Running this syntax for a week commencing field would return the values for the equivalent week in previous years.

Please note that when specifying the date used as a reference, the value must be wrapped in the TODAY syntax in the following format:

<Date Field>:"TODAY(<DATE>)<Differential>"

For example: +Transaction_Date:"TODAY(20/09/2021)-1DAY"
Will return all data for 19/09/2021

When using the YEAR_SAME_WEEK differential for a specific values, the value must be wrapped in the NOW syntax in the following format:

<Date Field>:"NOW(date value)-1YEAR_SAME_WEEK"

For example: +Transaction_Date:"NOW(07/04/2019)-1YEAR_SAME_WEEK"
Will return all data for 08/04/2018, the equivalent day of the same week in the previous year.

Dynamic Views

When configured by an administrator, Dynamic Views can be used to filter using distinct counts for selected fields using the following syntax:

+DYNAMICVIEW: "<Dynamic View Name>"

For example:

+DYNAMICVIEW: "Distinct Employees"

Escaping Special Characters

Escaping a character allows users to enter a character without it applying its function in the Query screen when performing field-specific searches.

The special characters include:

+ - && || ! ( ) { } [ ] ^ " % ? : \

For example to escape \, use \\ before the desired character.

To escape the other special characters, use \ before the desired character. They can also be used with wildcards, for example searching for a quote " character someone in a string would be: "*\"*"

Boolean Operators

Boolean operators allow terms to be combined through the AND, OR, NOT and REPLACE logic operators.

By default, queries will default to using the AND operator. To change the operator used, click the Cog icon above the search results and click Show Boolean Operators. The available operators will now appear above the search results and can be changed by clicking the desired option. To dismiss these options, click the Cog icon and click Hide Boolean Operators.

The selected operator can be overwritten by what is typed into the query bar. For example, if the operator is set to NOT but a search is conducted with +, AND will be performed.

OR

The OR operator links two terms and finds a matching document if either of the terms exist in a document.

AND

The AND operator matches documents where both search terms exist anywhere in the text of a single document.

+

Using this operator ensures that any term after the + symbol must appear somewhere in a field of a single document.

NOT

The NOT operator excludes documents from the search results that contain the term after NOT.

-

Using this operator ensures that any documents that contain the term after the - symbol is excluded from the results.

REPLACE

The REPLACE operator will replace all current search terms, excluding filters, with the newly selected search term.

Inverting a Query

Once a search has been performed, the Invert Query option can be used to quickly perform the exact opposite search, the equivalent of including a NOT in front of the search term.

To invert the current search, click the Cog icon then click Invert Query. The query bar will have a red border when this option is active.

Strict / Lazy Syntax

While in the Query screen, it is possible to switch between Strict and Lazy syntax for the current Index in the current session using the option exposed when the Cog icon is clicked.

With Lazy Syntax applied, if a field used within a query no longer exists or the name is incorrect (such as a spelling mistake or the field has changed name), the field is ignored and is not used as part of the query. With Strict Syntax applied, data is not displayed when there are incorrect field names in the query syntax. Instead, an error message is displayed.

Adding Results to a Query

To add a value displayed in the results panel to the current query, click it. The search will be automatically executed, allowing rapid filtering of large datasets.

Sorting Data

If enabled by the administrator, it is possible to sort the Query screen results using any available column.

Click the arrows below the column heading and choose between A-Z, Z-A or No Sort. The arrows will turn blue and highlight the sorting direction once a selection has been made.

Selecting Unique Column Values

To select one or more unique values from a column, first click the relevant column heading. This will display a dialog box containing the unique column values.

To display the record count for each unique value, click the Show Counts button. If the current search or filter restricts the values that are displayed in the unique column count, click Show Others to display all values restricted by the search.

With the Show Extra Calculations On Headers option enabled in the User Preferences, additional information for numeric fields is displayed. The minimum, maximum average and sum is calculated from the displayed values.

Click the … button to manually enter values or select the required values from the list and click Apply to complete the process.

External Query Links

CXAIR can provide links from data in the results panel to other applications.

If external query links have been enabled by the administrator, an Open button will be linked to each row of data in the results panel.

Display Fields

To add, remove or change the order of the columns displayed in the table view, click the Cog icon then click Display Fields.

Move fields between the Available and Selected boxes using the left and right arrows and use the up and down arrows to order the selected fields.

Click the Set as Default checkbox to save the display options as a default setting. Click Apply to save changes, or click Close to discard changes. 

Editing Index Components

As an administrator user, the following options allow fast access to each index component in the admin area.

Click the Cog icon to access the Edit Search Engine, Edit Index, Edit Data Source Group and Edit Data Source options. The Build Data Source Group option will build the current Data Source without having to navigate to the admin area.

Selecting Rows

To select rows, click the Cog icon then click Show Row Select Options. A checkbox will then appear next to each row to allow easier viewing of records when scrolling horizontally. Hide these checkboxes by clicking the Cog icon then click Hide Row Select Options.

Index User Preferences

Click the Cog icon then Index <Index Name> Preferences to access the user preferences for the currently loaded Index.

To automatically run a search term when the Index is selected and the query bar is empty, enter the required term in the top textbox. Alternatively, click Select Query to load a saved query.

Use the Display Query Results as drop-down list to specify the default Query layout, as detailed below, and specify whether to display columns that do not contain any values using the Display Blank Columns drop-down list.

Click the Mark All Reports as Read option to mark all new reports as read for the Index.

Query Layout

There are a number of different layout options for the results panel in the Query screen. Their availability is configured by the administrator.

Table

The default layout, displaying rows of data in the style of a worksheet.

List

Displays a summary of the data in a list format.

Unstructured

Used for indexes containing unstructured data such as documents, this view allows users to see the text in a view of wrapped paragraphs with header and title information similar to a table structure. This view will recommend other search terms based on the term entered into the query bar.

Fixed List

Another view for unstructured data, this view provides a web-style search results summary and will display each section in a uniformed structure.

HTML

Displays data using HTML formatting.

Word Cloud

Displays the data as a series of words, with the size of each word representing the relative amount of records for each value.

Word Cloud Configuration

To alter the word cloud options, click the Cog icon in the control panel located above the word cloud.

The word cloud window view is divided into two windows by default. Use the Window Width slider to adjust the size ratio. When set to the maximum, only a single word cloud is displayed. Type a value into Window Height textbox to control the size of the display in pixels. To specify options for individual windows, use the Left Window and Right Window tabs.

Type a required value in the Maximum number of words to display textbox to limit the number of words to display per page and specify the Minimum font size and Maximum font size options to customise the size range of the displayed text.

The Rotation drop-down list provides the option to rotate the words by a specified angle in degrees while the Random option will display fields in random directions and positions rather than in uniformed directions.

Enable the Percentage of Total option to size words based on a percentage of all data, and enable the Stop Words option to exclude the words configured by the system administrator.

To hide the contents of the query bar when viewing the word cloud, click the Hide search terms checkbox and to set any changes as the default configuration, use the Set as default radio buttons.

Click Apply to save changes, or click Close to discard any changes.

Control Panel

The control panel, located above the word cloud, is used for a variety of options.

To change the field used in a word cloud window, select the required value from the Field drop-down list. Use the icons located on the right of the panel to toggle between one and two windows and to select a variety of charts and colours.