Building new data sets with the Query transform

Objectives

After completing this lesson, you will be able to:

  • Build new data sets with the Query transform

Data Sets Creation

Aggregate functions are one set of built-in functions within SAP Data Services.

By using aggregate functions in the Query transform, you create a whole new data set in your target.

Aggregate Functions Overview

Aggregate functions:

  • Perform calculations on numeric values and some non-numeric data.
  • Require a set of values to operate.
  • Generate a single value from a set of values.
  • Are most commonly called from within a Query transform, not from custom functions or scripts.
  • Use the data set specified by the expression in the Group By tab of a query.

When you use an aggregate function, except if you want only one row as a result, you need to specify a Group By clause and all the elements in this Group By clause must also be present in the Schema Out.

You can specify more than one aggregated measure in the same Query transform if they all have the same grouping criteria.

Built-In Aggregate Functions

There are six different aggregation functions you can use in your queries.

List of Aggregate Functions

The table lists the names and descriptions of built-in aggregate functions:

FunctionDescription
avgCalculate the average of a given set of values.
countCount the number of values in a table column.
count_distinctCount the number of distinct non-null values in a table column.
maxReturn the maximum value from a list.
minReturn the minimum value from a list.
sumCalculate the sum of a given set of values.

Let's Try It

Let me guide you through the creation of an aggregation:

Log in to track your progress & complete quizzes