Attributes for Custom Tables filters
Custom Table attributes include all fields defined for all Custom Tables associated with your Customer Data List.
Tips for creating filter conditions
When specifying conditions for Custom Table filters, keep in mind the following:
- Drag and drop the table from the Custom tables column to the Custom Table Attributes region. You can include the table multiple times when building your conditions.
- To ensure that the table has at least one record to filter, select is more than or equal to and enter 1 in the number field. Configure the date criteria to specify the date ranges you want to filter.
- Click the Refine icon to add the criteria from the custom table and lookup tables joined to it. You can include up to ten criteria for the "Where" part of each table. The criteria you see in the list are the columns in your table and columns in any lookup table joined to the custom table. Click the reveal arrow to show the columns for a given custom table. In the example below, we've selected PRODUCT_CATEGORY from the Product Info table, which is a lookup table for the Order Line Item table.
- For your criteria conditions, enter the value. If your attribute is from a lookup table (that is, a table in the Define Criteria list that isn't the one you dragged and dropped onto the Custom Table Attributes region), you can start typing the value into the field. The list will automatically search for and show values that you can select. Type-ahead search is not supported for custom tables that are not lookup tables.
- Click the fx button to select an aggregation function: SUM, AVERAGE, MIN, MAX, or COUNT. To select an advanced aggregation function, select Comparative Position/Range. For numerical and non-numerical data, you can aggregate data and filter records based on percentages or number of records. Learn more about Advanced aggregation.
- Form your conditions by selecting the operator and the value. The AND and OR operators will be the same between tables and within a table's "Where" statement. If you have a table for each segment, and then change AND to OR between the first two, all of the ANDs will change to ORs. The same occurs within a table.
- The "does not contain" condition applies only to records that contain a value, not for records with a null value. This means that your result set will include records that contain any value except the specified one, but not records with no values. For example, the rule “State does not contain California" will include records in New York, Pennsylvania, and Texas, but not records in California nor ones that contain no value. To include empty records as well, add OR "State" - "is null” to the rule.
- "By Date" condition date fields are all initially set to the current date. The “Between” condition includes the first and last dates of the defined date range. By default, both fields are set to the current date.
Note: SQL queries using the BETWEEN condition do not include the final day in a date range. To ensure consistency, add a + 1 to the SQL end date.
