MarketWide has several dialogs and modules that are consistent across the entire software suite. In this Sectionsection, you will find some of the most common areas that you will encounter while using MarketWide.
Topics:
| Table of Contents |
|---|
Filters
At times, you might want to edit or analyze a table or view, but you're only interested in a group of rows or records within that table. If you wish to edit or analyze a group records, a filter can define which records are returned to you. If you want to work with the entire table, you probably wouldn't use a filter.
For example, from your total customer database, you may only want to see a list of female customers from Pennsylvania who spent $100 or more. In this case, you would use a filter.
In MarketWide, the filter criterion is entered in the Filter dialog, which is accessible from all MarketWide programs by clicking the Filter (
) toolbar button. The output of the report you create with MarketWide is limited by the criteria you enter in the Filter dialog.
| Info | ||
|---|---|---|
| ||
Adding a filter, even a small one, can be a great way to reduce processing time while running queries and campaigns. |
Creating a Filter
In MarketWide, you create filters using the Filter dialog, which is accessible from any MarketWide application. A filter consists of one or more Statements which includes include the following components:
- Field -A field from a table or an Expression for which you want to set certain criteria.
- Condition - An operator that indicates the relationship between the Field and Value.
Value - A field, expression, or constant to test against the Field. Values are entered via the Value dialog.
| Tip | ||
|---|---|---|
| ||
|
| Info | ||
|---|---|---|
| ||
You can drag and drop fields from the left pane to the right pane or double-click a field in the left pane to add a new statement to a filter quickly. |
Types of Conditions
Conditions represent relationships between fields and values. For example, suppose you want to view all customers who have made purchases of $100 or more. You would need to establish a relationship between the Purchase Amount field and the value 100 (the relationship being that the Purchase Amount is greater than or equal to 100).
Conditions may be any of the following:
Condition | Symbol | Number of Values | Example |
|---|---|---|---|
Equal To | = | 1 | State = 'CA' |
Not Equal To | <> | 1 | State <> 'CA' |
Greater Than | > | 1 | Quantity > 3 |
Less Than | < | 1 | Quantity < 3 |
Greater Than or Equal To | >= | 1 | Quantity >= 3 |
Less Than or Equal To | <= | 1 | Quantity <= 3 |
Between | BETWEEN | 2 | Age BETWEEN 20 AND 30 |
Not Between | NOT BETWEEN | 2 | Age NOT BETWEEN 20 AND 30 |
In | IN | 1 & Up | Status IN ('A','I','P') |
Not In | NOT IN | 1 & Up | Status NOT IN ('A','I','P') |
Like | LIKE | 1 | Zipcode LIKE '207%' |
Not Like | NOT LIKE | 1 | Zipcode NOT LIKE '207%' |
Is Null | IS NULL | 0 | Title IS NULL |
Is Not Null | IS NOT NULL | 0 | Title IS NOT NULL |
EQUAL/NOT EQUAL: Tests for exact matches. The two values being compared must be identical for this condition to be true. NOT EQUAL is true when values are not identical.
GREATER THAN/LESS THAN: Ensures that the rows selected are greater than or less than the entered value. For numeric fields, standard arithmetic rules are used. For character data, an alphabetical comparison is used (e.g., A is less than B).
BETWEEN/NOT BETWEEN: Used to test for a range of values. For example, ACCOUNT_NUMBER BETWEEN 12345 AND 54321 asks for all of the account numbers between, and including, those two named. Likewise, ACCOUNT_NUMBER NOT BETWEEN 12345 AND 54321 asks for all of the account numbers except for the ones between, and including these two.
IN/NOT IN: Used to test for one or several values. For example, STATE IN ("ME","VT","NH") asks for information only in Maine, Vermont, and New Hampshire. NOT IN excludes values. STATE NOT IN ("ME","VT","NH") returns information from all states except Maine, Vermont, and New Hampshire.
IS NULL/IS NOT NULL: Searches for null (or missing) values. If you use IS NULL or IS NOT NULL, you do not have to cannot enter a value. (In fact, MarketWide does not allow you to do so.) This This menu option is not available if the selected Field does not support nulls.
LIKE/NOT LIKE: These conditions are useful for doing wild card searches but are only available for character searches. The LIKE operator lets you search character data using partial matches. For example, you might want to retrieve all names containing the word "corporation."
Wildcard Character: The wild card character is a percent sign (%), which causes the comparison to skip over (or ignore) zero or more characters. Just as a wildcard in a poker game can represent any card, the % wildcard can be any character(s). Possible wildcard searches:
- ZIPCODE LIKE '609%' - searches for every ZIP code that begins with "609."
- NAME LIKE '%CORPORATION%' - retrieves all records that contain the string "CORPORATION" anywhere within the name.
- KEYCODE NOT LIKE 'D%' - retrieves all records that do not have keycodes starting with "D."
Entering a Value
The filter value is the constant, or condition, that you want to compare your field against. Constants can either be expressions or other fields. Depending upon the type of condition that you have selected, you can enter from zero to many values. Once you click in the value area in a statement, the Enter Value dialog appears. This dialog offers the following options:
- Add – Adds the constant to the Value list.
- Delete – Deletes a highlighted value from the Value list.
- Clear – Erases all values from the Value list.
- Copy – Copies the highlighted list of values. If nothing is highlighted then you will copy a blank list.
- Paste – Pastes values copied from another source (MarketWide QueryBuilder, Excel spreadsheet, etc.) the into the Value list. Keyboard shortcuts 'Control > C' for Copy and 'Control > V' for paste also work in MarketWide to expedite data entry.
- Fields – Allows you to select another field as the value. For example, you may want to select records where the SHIP_DATE = ORDER_DATE. In this case, the ORDER_DATE field serves as the value.
- Expressions – Opens the Select Expression dialog, allowing you to choose an expression to use as the value.
- Picklist – When available, provides a list of descriptions for the selected field. They are displayed in unabbreviated form (e.g., "Maryland" is displayed instead of "MD"), but once selected, MarketWide inserts the corresponding code into your filter. This option is not available for fields that do not have picklists. See "Picklists" later in this manual for more information on using picklists.
- Lookup – Like the Picklist option, the Lookup feature allows you to choose a value from a lookup table (a table containing codes and their descriptions). But unlike Picklist, you can perform a filter on the Lookup table to work with more specific criteria. This is unavailable if the field does not have an attached lookup.
- Frequency Distribution - Frequency distribution shows the values stored in the database for the specified field. This can be useful when trying to remember the values for a field that does not have a picklist. The frequency distribution is persistent, which means once downloaded the list remains available until changed on the host. Essentially, because the list had already been downloaded, there is no need for a query to be generated every time the button is clicked. The result of this is a significantly shorter wait time for the list to load.
- Variable Builder- Opens the Variable Builder dialog. These variables can be used instead of a manually entered value in cube and TouchPoint query criteria.
- Prompt Me- When included in a query or campaign, a prompt will appear requiring the user to enter a value specific for that variable when the query or campaign is submitted.
| Tip | ||
|---|---|---|
| ||
|
| Info | ||
|---|---|---|
| ||
Pressing the <Enter> key does the same thing as clicking the Add button. |
| Note | ||
|---|---|---|
| ||
|
Entering Fields as Values
As mentioned earlier, a value is the standard to with which you compare a field using a condition. In the statement, STATE = CA, STATE is the field, EQUAL is the condition, and CA is the value.
Occasionally, you may need to compare one field to another. For example, if you want to view all records in which an order was placed and shipped on the same day, you would create the filter statement SHIP_DATE = ORDER_DATE. In this case, the ORDER_DATE field serves as the value for comparison to the SHIP_DATE.
The Select Field Dialog
The Select Field dialog, which displays all fields in the table you are querying against, allows you to choose a field as the value in a statement.
Select Field
The Select Field dialog is used to select a database field and lists all the fields available for selection as well as various details about the fields. It is available in the Value Select dialog and in the TouchPoint Template Wizard to select the default constraint.
- Name - The name of the database field.
- Alias - The user specified field name.
- Description - A brief description of the data the field contains.
- Math - Allows mathematical functions to be performed on the value of the field.
- Group By - Indicates if this field can be used in a group by clause- also called dimensions- in Analyzer and QueryBuilder.
- Composite - Indicates that several fields are linked together to be used as a primary key.
Email Distribution List Setup
The Email Distribution List Setup is available through Analyzer and TouchPoint and is used to set up an e-mail distribution list for output reports. This can be used to send out an email to indicate when a report has finished running, or even to automatically sending send out the finished report to multiple people.
Paired with Scheduler, this enables you to entirely automate a report by scheduling ahead of time when a report will be run, and automatically sending it to a predetermined group of people.
| Tip | ||
|---|---|---|
| ||
|
The Email Distribution List Setup Toolbar
Icon | Description |
|---|---|
| Opens the Open Report dialog. |
| Saves the current settings. |
| Clears everything in the Email Distribution List Setup dialog. |
| Copies the selected text to the Windows Clipboard. |
| Pastes the text from the Windows Clipboard. |
Select Field
The select field dialog is used to select a database field and lists all the fields available for selection as well as various details about the fields. It is available in the value select dialog and in the TouchPoint Template wizard to select the default constraint.
. |