Working with conditional grouping transformation
You use conditional grouping transformations to create aggregated summaries of result set data based on conditional expressions.
About this task
|Department||Total Salary||Matched Employees||Total Employees|
This conditional grouping was done by carrying out the following steps:
Nameas grouped columns.
Departmentwas added as a GROUP and
Namewas added as a COUNT. The
Namecolumn allows us to include a column showing total number of employees in a department, providing a contrast with the number used to perform the salary summation.
- A filter was defined to select employees with 5 or more years service.
- Two conditional columns were added, both driven by the above filter.
Total Salaryperforms a sum of the
Matched Employeesperforms a count of the
- Finally, the
Namecolumn in the result set was edited to display
Total Employeesvia the Design tab (right-click the column and select Change Column Heading from the context menu).
To configure a conditional grouping transformation:
- In the Analytical query structure, select
a conditional grouping transformation node. In the Item editor pane, you can view the items of the conditional grouping transformation. The columns from the source data set are displayed in the Group section.
- Optional: If the selected conditional grouping transformation is not populated yet, add a query or table into the <none> node. For more information, see Populating analytical query transformations.
- In the Group section, select check boxes next to the columns
that will provide rows for grouping the aggregated results. Note: Use the Include All Columns check box to select or deselect all available columns.
- In the Aggregation column, click the value and select an aggregation type for the selected column from the list.
- Optional: You can configure the grouping of records in case sensitive or case
insensitive manner by clicking on the Collate Option button. Specify
Collate Option for each group column by selecting case sensitive or case
insensitive option from the drop-down provided. Note: Collate Option button is enabled when Grouping column is of character data type and Aggregation is of type Group.
- In the Conditional Grouping section,
click Add Filter. The Filter Wizard opens.
- Specify a name for the filter in the Filter
name field. It is a good idea to give the filter a name that reflects its function, for example,
Older Groupfor the filter that will display employees that have worked more than five years.
- Select a filter type and click Next.
- Build your filter using the table of options or the expression
designer and click Finish. The Filter Wizard closes and the filter that you specified is displayed in the Filters table.
- Above the Columns table, click Add Column.
- From the list in the Source Column column, select the column that you want to filter. This column will provide the values that will be filtered and aggregated across the group columns.
- Select the filter that you will use with this column from
the Filter list. This list is populated with all of the filters that you built in the Filters table.
- In the Aggregation column, click the value and select an aggregation type for the values in the selected column.
- Specify a name for the column of aggregated values in the Column name field.
- Once you have finished adding all of the filtered value
columns that you want to display, click the Run Query toolbar
button. The query is run and the results are displayed in the Results editor. You will notice that there is a column for every group and value column that you selected. The value column headings display the column names that you specified. The contents of the value columns represent all of the values that are included in the conditional filters that you set and are aggregated according to the methods that you selected.
- Optional: To add another transformation to the current analytical query structure, follow the procedure described in Building analytical query structures.