MarketWide has several dialogs and modules that are consistent across the entire software suite. In this section, you will find some of the most common areas that you will encounter while using MarketWide.
Topics:
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.
Time Saver
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 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.
To Build a Statement in Your Filter
- In the left pane of the Filter dialog, highlight a field to use in a statement.
- Choose Edit > Add Statement from the menu or click on the Add Statement toolbar button to add a statement to the right pane.
- Click the Condition area (EQUAL by default) and select a test condition from the drop-down list. For more information on test conditions, see Types of Conditions.
- Click the Value area and enter a value in the Value dialog. See Entering a Value for more information.
- When finished, click OK to save your changes.
Time Saver
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 cannot enter a value. 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.
To Enter a Value
- In the Enter Value dialog, perform one of the following actions:
- If you know the value you want to enter, you may simply type it in the Enter Value text box and click Add.
- If you copied a value(s) from another source, click Paste to paste the copied values into the Value list.
- If you are unsure what value to use, click Lookup or Picklist to find a value, then click OK. See Picklists and Lookups later in this manual for more information.
- If you want to compare fields, click Fields, highlight a field, and click OK. See Entering Fields as Values later in this manual for more details.
- To select an expression, click Expression, highlight an expression, and click OK.
- Click OK to save your changes and exit the Enter Value dialog.
Time Saver
Pressing the <Enter> key does the same thing as clicking the Add button.
Note
- Your MarketWide administrator can configure MarketWide so that certain fields in your database are read-only. Read-only means that you cannot enter values directly in the Enter Value area of the Enter Value dialog. This ensures changes are not made. You must use the Picklist or Lookup button to select a valid value for your query. For information on Read Only fields in your database, contact your MarketWide administrator.
- If you would like to select more than one value to copy then hold down Ctrl while clicking on the desired items. To copy the whole list, click on the value at the top of the list. Then hold down shift while clicking on the last item in the list.
- Try not to use the Frequency Distribution option on consecutive fields such as dates and consumer numbers. For example, if you select frequency distribution for an order date a relatively useless list like this will be returned: October 1, 2015, October 2, 2015, October 3, 2015 etc.
Entering Fields as Values
As mentioned earlier, a value is the standard 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 to automatically 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.
To Use Email Distribution List Setup
- Auto Generate Report must be enabled.
- In the Email Distribution List Setup dialog, under Subject type in what the subject of the email should be.
- Under To: fill in the email address you would like to receive the email. Note, only one email address per line.
- Under Message, type in what you would like for the body of the email.
- Ensure Include Attachment is selected to include the report as an attachment to the email.
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. |
