Query Part Export Settings
- Active - will export data when the query runs.
- Inactivate - will temporarily disable the export without being deleted.
||The name of the export is used as the column heading.
||Set a numeric (positive integer) width to truncate the column at a particular length. The width setting must be used for all exports in a query that is configured to generate a fixed-width file.
||Custom fields support different value translations:
- Value - Export the normal field value for the custom field. If the field uses prompts that are configured with a Short Value, that short value will be exported.
- Export Value[2-5] - For prompt-based custom fields, export the Export Value (or Export Value 2, 3, 4, or 5) configured in the Prompts Admin Tool.
- Index Value - For prompt-based custom fields, export the Index value of the prompt.
- Extended Value - Export the full value of the field, even if a Short Value was configured for the prompt.
- Category - For prompt-based custom fields, export the Category value of the prompt.
- Prompt Order - For prompt-based custom fields, export the Order of the prompt.
- PKV - This is a special use only setting to be used for prompt-based custom fields that contain PKVs (key-value pairs) to export the value (within the <v> tag) of the key entered in the Key field.
- Timestamp - Export the timestamp that the field was set for the record. This can be used in conjunction with the Date or DateTime Format Types (see below).
- Translation Value[2-5] - Use Translation Codes to translate the values of any export to a different value.
||For custom fields that are configured to store multiple values:
- All Field Values will export each value in the same column. Configure a Separator to delimit the values in the field. For example, for a field with three values: A, B, and C with no separator will export as ABC. Using | (pipe) as the separator will export as A|B|C.
- Single Field Value at Selected Index will export just one value. Use the Index Number (positive integer) to specify which value should be exported for this particular column in the export. For example, you may wish to export race information with one race in each column. You may add the Race export 5 times and configure the first export with Index Number 1, the second with Index Number 2, etc. For ordered fields (configured in the Fields Admin Tool), the Index Number will be equal to the order stored on the field. For unordered fields, the values are ordered alphabetically.
||A format type, sometimes in conjunction with a format mask, allows you to transform the value for a particular export in various ways.
- Bit : To use custom values for a bit prompt (True/False), add a format mask that begins with the value to use for true values, followed by a comma, followed by the value to use for false values. For example, to replace True/False values with "Yes" or "No", select Bit and add a format mask of Yes,No.
- Date: Converts the value to the SQL Date type. Add a format mask using the date formatting options listed below.
- DateTime: Converts the value to the SQL DateTime type. Add a format mask using both the date formatting options and the time formatting options listed below.
- Real: Converts the value to the SQL Real type. Add a numeric format mask such as 0.## to format numeric values.
- Int: Converts the value to the SQL Int type. Add a numeric format mask such as #,### to format numeric values.
- Money: Converts the value to the SQL Money type. Add a numeric format mask such as $#,### to format numeric values.
- String (Upper Case): Converts a string to upper case (ALL CAPS).
- String (Lower Case): Converts a string to lower case (no caps).
- String (Proper Case): Converts a string to proper case or title case (Initial Caps).
- Distribution: Use the Interval to enter an integer to indicate how many groups you want the values divided into. The Distribution format will then export which group a particular value would belong to. For example, to show which quartile a value falls into, select Distribution and enter an Interval of 4. This will be particularly useful for Predict queries.
- Custom SQL (use @val): Use the Format Mask to enter custom SQL that includes "@val" somewhere in the SQL. The "@val" will be replaced with the actual value. For example, to always output the value incremented by 1, you could enter a format mask of try_convert(int, @val) + 1.
Another example is if you want to export a phone number with all non-digit characters stripped out: dbo.regexReplace(@val, '[^\d]', '') This is performing a regular expression replacement, where any non-digit is replaced with an empty string.
Other functions can also be used to wrap @val, including the standard replace function.
||A format mask value is used to transform or format a query result. The mask will be applied in different ways depending on the format type selected. See Format Type above.
||Enter a value to substitute when no value exists for a particular export (e.g. "None" or "Unknown" or a default value).
|Date Formatting Options
||Month in Words with Day (e.g. September 14)
||Month in Numbers (e.g. 09)
||Abbreviated Month in Words (e.g. Sep)
||Month in Words (e.g. September)
||Full Date in Numbers (e.g. 9/14/2015)
||Full Date in Words with Weekday (e.g. Monday, September 14, 2015)
||Day in Numbers (e.g. 14)
||Abbreviated Day in Words (e.g. Mon)
||Day in Words (e.g. Monday)
||Month + Year in Words (e.g. September 2015)
||Two digit Year (e.g. 15)
||4 Digit Year (e.g. 2015)
|Time Formatting Options
||Hours (e.g. 05)
||Minutes (e.g. 52)
||Seconds (e.g. 13)
You can add text to the format by wrapping it between single quotes:
dddd, MMMM dd ‘in the year’ yyyy --> Monday, June 15 in the year 2015 at 10:15 PM