Computing data using built-in functions

After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Use functions in expressions

Built-In functions

When you map columns in the Query transform, you can use expressions to modify the source value.

Those expressions could, for example, change the data format or even do some calculations.

Data Services comes with a set of built-in functions that you can use in those expressions:

Built-in Function Categories

Built-in functions are organized into categories. Here are the most commonly used:

Function TypeDescription
AggregatePerforms vertical calculations on numeric values, dates, strings, and other data types.
ConversionConverts values to specific data types.
DateCalculates and converts date values.
LookupLooks up data in other tables.
MathPerforms complex mathematical operations on numeric values.
StringPerforms operations on alphanumeric strings of data.

Smart Editor

To help you code the mapping expression, you can use the Smart Editor.

You access the Smart Editor by choosing the three dots icon.

The Smart Editor has a user friendly interface as shown in the previous figure. Use drag and drop to move objects.

Follow these steps to use the Smart Editor:

  1. The Data tab allows you to fetch.
  2. Open the object in which you want to use an expression.
  3. Select the three dots icon to show the Smart Editor.
  4. Select the Functions .tab and expand a function category.
  5. Select a function and look at its description in the bottom left pane.
  6. Drag and drop the chosen function in the expression editor.

Function Wizard

Some functions are more complex than others and need a lot of parameters. You can then use the Function Wizard to help you set those parameters.

Follow these steps to use the Function Wizard:

  1. Open the object in which you want to use an expression.
  2. Select Functions to open the dialog box.
  3. Select a category from the list of functions in the dialog box.
  4. Select the chosen function.

The Function Wizard's user prompt interface is shown in the previous figure. The interface is similar to the Smart Editor and contains the following features:

  • Drop down menus
  • Parameters tailored for each function

Let's Try It

Let me guide you through the use of the function wizard:

If you want to test it by yourself, go ahead:

Log in to track your progress & complete quizzes