âShow Table of Contents
You are here: Tools > All Tools > Cross Tab
About
the CrossTab Tool:
The CrossTab pivots
the orientation of the data table. It transforms the data so vertical
data fields can be viewed on a horizontal axis summarizing data where
specified.
The CrossTab tool is the reverse application
of the Transpose Tool which pivots the orientation
of the data from horizontal to vertical.
There is no limit to the amount of records or fields that can be put
through the CrossTab tool.
Configuration Properties:
- Select the Grouping Field(s) to use in performing
the CrossTab operation. A list of all available fields will appear
in the window. Check the box that corresponds to any field(s) to GROUP
on.
The group by process combines
database records with identical values in a specified field into a
single record. All of the resulting data from the records in a group
are then summarized.
- Choose the Header Field from the drop down list. The
Header field is records whose header field contains identical values
will be summarized together in a new output field for the same group.
These are the fields that will appear across the horizontal axis of
the new table.
- Choose the Data Field to populate the Header
Field, selected in Step2. The data field contains the values that
will be summarized according to the methodology specified in step
4.
- Select the Methodologies for handling data fields.
At least one Methodology MUST be selected. Choices in this list depend
on the data field type chosen
in step3
Choices for String fields are:
- Concatenate
: Strings will be arranged into one field, separated by the
specified separator (step 5) and up to the specified field length
(step 6)
- First:
The output will be the first found value within the unique group
and header field
- Last:
The output will be the last found value within the unique group
and header field
Choices for Number fields are:
- Sum: Sums
the data fields
- Average:
Calculates an average based on the values of the data fields
- Count:
Count of records in unique group and header field.
- Percent Row:
Calculates a percent based on the value of the data field as it
pertains to the data values of the row after the CrossTab.
- Percent Column:
Calculates a percent based on the value of the data field as it
pertains to the data values of the column after the CrossTab.
- Total Column:
Totals all data fields within a group
- Total Row:
Adds a new row containing a grand total
Note: when choosing one or more of the methodologies
above, the abbreviation for that process will be appended to the field
name, example: AVG_FieldValue.
UNLESS only one
of the following methodologies: SUM,
First or Last
are selected. In which case, nothing is appended.
- Notice
the String Separator box, this setting is specific for concatenating
strings.
The character
specified in this box will automatically be inserted into the concatenated
string where those strings were joined. You can insert any character
here, or leave blank.
- Notice the Field Size box, this is the
maximum field length for concatenating strings. If the string is larger
than the size specified, a warning will appear in the Results window
and the data will be truncated to the extent of the specified field
size.
Click Apply
to have the configurations accepted.
Note:
For information regarding Input, Output, Annotation and Error Properties,
see Tool Properties.
Related Topics