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

With conditional grouping transformations, you can create filters that control what values from a given value column are included in the aggregated summary. For example, you have a table that contains staff data for your company. You create a query that displays the sum of the salaries of all of the employees in a given department across each job type. Now, you want to only display the sum of the salaries of employees that have been working for five or more years. You can use a conditional grouping to create a filter that only includes in the aggregation salary values from rows with a value of 5 or more in the YEARS column.
Table 1. Original data
Department Name Years Salary
SALES Smith 12 60
SALES Edwards 7 40
SUPPORT Jones 6 40
R&D Adams 2 25
R&D Baker 7 50
Using a conditional grouping, the result set can be transformed into the following:
Table 2. Conditionally grouped data
Department Total Salary Matched Employees Total Employees
SALES 100 2 2
SUPPORT 40 1 1
R&D 50 1 2

This conditional grouping was done by carrying out the following steps:

  1. Adding Department and Name as grouped columns. Department was added as a GROUP and Name was added as a COUNT. The Name column 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.
  2. A filter was defined to select employees with 5 or more years service.
  3. Two conditional columns were added, both driven by the above filter. Total Salary performs a sum of the Salary column and Matched Employees performs a count of the Name column.
  4. Finally, the Name column in the result set was edited to display Total Employees via the Design tab (right-click the column and select Change Column Heading from the context menu).

To configure a conditional grouping transformation:

Procedure

  1. 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.
  2. 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.
  3. 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.
  4. In the Aggregation column, click the value and select an aggregation type for the selected column from the list.
  5. 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.
  6. In the Conditional Grouping section, click Add Filter.
    The Filter Wizard opens.
  7. 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 Group for the filter that will display employees that have worked more than five years.
  8. Select a filter type and click Next.
    The next page of the wizard depends on your previous selection.
    For more information about building filters, see:
  9. 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.
  10. Above the Columns table, click Add Column.
  11. 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.
  12. 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.
  13. In the Aggregation column, click the value and select an aggregation type for the values in the selected column.
  14. Specify a name for the column of aggregated values in the Column name field.
  15. 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.
  16. Optional: To add another transformation to the current analytical query structure, follow the procedure described in Building analytical query structures.

Results