Analytics - Guidance
Simple queries
The query page has two input fields. In the upper field, queries based on concept names can be entered. For example, 'CashBank > 10000000' (without the quotes) will identify all accounts satisfying that constraint. More complex queries can be constructed, eg 'CashBank > 10000000 and CreditorsDueWithinOneYear < 1000000'. The inequality comparator is '<>'.
For boolean values, use 'true' or 'false' (no quotes) eg 'EntityTrading = false'. For string literals use double quotes, eg 'EntityCurrentLegalOrRegisteredName = "somecompany ltd"'. For dates, use yyyy-mm-dd format eg 'DateApprovalAccounts >= 2000-01-01'.
As you begin to enter text in the upper field, a menu of matching concept names will appear. A name can be selected directly from the menu to minimise typing. Typing 'ban' for example lists all concept names starting with 'Bank'. Starting with '' will match the partial name anywhere, so for instance typing 'ban' will produce a menu that includes CashBank.
For string values, a partial match can be requested. So for instance 'EntityCurrentLegalOrRegisteredName like "ABC"' will match company names like 'ABC Limited' or 'ABC Group Ltd' where '' matches any number of characters. '?' matches exactly one character, so 'EntityCurrentLegalOrRegisteredName like "AB? Limited"' will match company names like 'ABB Limited' or 'ABD Limited' but not 'AB Limited'. All string comparisons are case-insensitive.
When the query is submitted, up to 50 results will be displayed directly; if there are more than 50 results the user is given the option to display them.
A result set can be downloaded as a file of comma separated values (.csv) to import into a spreadsheet etc.
Concepts can also be compared directly, as in 'FixedAssets < CashBank'. Comparisons can be combined using 'and', 'or', 'not' and round brackets.
The lower input field is for free text queries using the lucene query engine. These queries are applied to the visible text on the face of the accounts. Some examples:
| Query | Result |
|---|---|
| eggs | Finds documents containing the word 'eggs' |
| eggs bacon | Finds documents containing the word 'eggs' or the word 'bacon' |
| +eggs +bacon | Finds documents containing the word 'eggs' and the word 'bacon' |
| +eggs –bacon | Finds documents containing the word 'eggs' but not the word 'bacon' |
| "bacon and eggs" | Finds documents containing the exact phrase 'bacon and eggs' |
Note the use of double quotes to mark a phrase.
The two types of query can be used together, using the 'and/or' radio buttons on the form to specify the conjunction. An example would be 'CashBank > 1000000' in the upper field, and 'liquidation' in the lower (no quotes in either case).
Advanced usage
More complex queries can be constructed by ignoring the lower input field and adding free-text query terms to the upper field enclosed in { curly brackets }. The example above would thus be 'CashBank > 1000000 and { liquidation }' (without the quotes) in the upper field. More than one free-text term (each enclosed in { } ) can be added in this way and combined into the query using "and', 'or', 'not' and round brackets.
The lucene free-text query engine can handle wildcards, proximity matching and other subtleties. See this link for details; ignore the section headed 'Fields' in the reference. Remember that each lucene query term must be enclosed in { curly brackets } if it is in the upper input field.
