Aggregate Operator

Estimated Reading Time:  8 minutes

Overview

The Data Workflow Aggregate operator lets you perform 1 of 5 operations on your data set:

  • Sum By: Adds all numbers in a given range.

  • Count By: Counts the number of values in a given range that contain numeric values.

  • Mean By: Calculates the average of values in a given range.

  • Max By: Finds the highest value in a given range.

  • Min By: Finds the lowest value in a given range.

Say you have a data table that tracks details on various dogs. A column in that data table might be Weight. The Aggregate operator can find the sum of every dog's weight in that column.

Similar Data Workflow Operators

The Aggregate operator is similar to the Append operator. Here are the operators compared:

  • Aggregate operator: Performs calculations on 2 or more numbers. This operator supports the Sum By, Count By, Mean By, Max By, and Min By operations.

  • Append operator: Combines strings or arrays together. When appending strings, acts as a concatenation. When appending arrays, joins both arrays into one.

What You'll Learn

In this article, you'll learn:

About the Info Window

Here's what an Aggregate operator looks like, along with its Info window:

And here's a breakdown of each setting in the Info window:

Setting

Description

Label

Sets the label for your operator. The label you enter here shows beneath your operator on your Data Workflow canvas.

This field is optional, but set a label if you use more than one of the same operator type. A label helps you identify your operators without opening any Info windows.

Aggregation Type

Sets the operation to perform on your data. You have the choice of:

  • Sum By: Looks at your Value Key field and adds all numbers in the range.

  • Count By: Looks at your Value Key field and counts the number of cells containing numbers.

  • Mean By: Looks at your Value Key field and calculates the average value.

  • Max By: Looks at your Value Key field and finds the highest value.

  • Min By: Looks at your Value Key field and finds the lowest value.

You can only choose 1 operation for each operator. But you can have as many Aggregate operators in your Data Workflow as you need. You can use labels to keep track of each operator.

Join Group Keys

When you select YES, your operator creates a new column that joins the values of your individual Group Keys.

For example, say you're working with the dog information data set from above. You might have columns for age and breed. If you aggregate these 2 columns, selecting YES returns a column called age-breed. And your resulting values display in the same format: 12-chow.

Group Key

A Group Key lets you separate your selected operation into categories. Say you want the mean weight of dogs in your data table. But you want to find each breed's mean weight rather than all dogs together. The Group Key lets you do that.

For example, if you have a column labeled breed, enter breed in the Group Key field. This tells your operator to treat each unique value in the breed column as its own group. Your Data Workflow then returns a mean value for each group.

To enter more than 1 Group Key, separate each using a comma.

TIP  To see a Group Key in action, visit the Aggregate Operator: Count By Use Case article.

Value Key

This is the column's label in your data table that you want to perform your selected operation on. This tells your Aggregate operator where to look and what to base its decisions on. Say you're finding the sum of values in a column labeled cost. To do that, enter cost as the Value Key. And to find the greatest value in a column labeled age, enter age. The same goes for any other Aggregation Type.

NOTE  Your Value Key is case-sensitive and must match the capitalization of your column label.

Adding a Sum By Aggregate Operator

In this example, you'll create a Data Table that holds the individual weight for several dogs. You'll then create a Data Workflow that adds each individual weight together.

TIP  This use case focuses on the Sum By function of the Aggregate operator. You can find other use cases in the Resources section of this article.

Here's how the completed use case looks in the Module Editor:

Here's how the completed use case looks in Express View, including a view of the DevTools Console:

What You Need

To set up this use case, you’ll need:

  • 1 Data Table component

  • 1 Button component

  • 1 Data Workflow component

To set up your Data Workflow, you'll need:

  • 1 Input operator

  • 1 Aggregate operator

  • 1 Console operator

NOTE  These instructions assume you have a new module open and saved with a title.

Configure the Data Table Component

First, add your Data Table. This holds the data you bring into your Data Workflow. Here, set up a list of dogs, storing their name and weight.

1. Drag and drop a Data Table onto your canvas.
2. In the Label and Property Name fields, enter dtDogs.
3. In the data table, enter the following:

A

B

name

weight

Ivy

60

Lucy

80

Wally

20

Nacho

10

Thor

50

Holly

100

4. Click Save.

Configure the Data Workflow Component

Next, add your Data Workflow component. Bring your data into the Data Workflow using an Input operator. Then, add an Aggregate operator to find the sum of your entire weight column. Finally, you can see that value using a Console operator.

1. Drag and drop a Data Workflow onto your canvas. Place your Data Workflow below your Data Table.
2. Enter dwfWeight in the Label and Property Name fields.

Configure the Input Operator

1. Drag and drop an Input operator onto your Data Workflow canvas.
2. Configure the Input operator's Info window as follows:

Setting

Value

Category

Input

Component

dtDogs

Required

Yes

Source

Default

Configure the Aggregate Operator

1. Drag and drop an Aggregate operator onto your Data Workflow canvas.
2. Configure the Aggregate operator's Info window as follows:

Setting

Value

Category

Aggregate By

Label

Total Weight

Aggregation Type

Sum By

Join Group Keys

No

Group Key

 

Value Key

weight
3. Connect the output port (right) of the Input operator to the input port (left) of the Aggregate operator.

Configure the Console Operator

1. Drag and drop a Console operator onto your Data Workflow canvas.
2. Configure the Console operator's Info window as follows:

Setting

Value

Category

Console

Label

What is the total weight for all dogs?
3. Connect the output port (right) of the Aggregate operator to the input port (left) of the Console operator.
4. Click Save.

Configure the Button Component

Now, add a Button component to launch the whole operation. Clicking the button fires your Data Workflow. From there, your Data Workflow takes care of the rest by calculating the summed weight.

1. Drag and drop a Button component onto your canvas. Place the Button above your Data Workflow.
2. In the Property ID field, enter btnTotalWeight
3. In the Label Text field, enter Find Total Weight.
4. From the Action Type, select Event.
5. In the Trigger on Click field, enter dwfWeight.

6. Click Save.
7. Save your module.

Now, preview your module in Express View and open the DevTools Console. Next, click your button. You can see the total weight displayed with the label from your Data Workflow: What is the total weight for all dogs?

Editing an Aggregate Operator's Settings

You can revisit and make changes to this operator.

1. Click the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2. Click the (Settings) button to open the Data Workflow canvas.
3. Click the operator to open its Info Window.
4. Make changes to the operator's settings as needed.
5. Click Save.
6. Save your module.

Copying an Aggregate Operator

You can make a copy of your operator using familiar keyboard settings. You can copy and paste an exact copy of your operator, matching all settings.

1. Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2. Click the (Settings) button to open the Data Workflow canvas.
3. Click the operator you want to duplicate.
4. On your keyboard, press Command + C (Mac OS) or Ctrl + C (Windows/Linux) to copy the operator.

TIP  You can copy more than one operator at a time. Hold Command or Ctrl on your keyboard and click all the operators you want to copy. Follow the rest of the steps as usual.

5. On your keyboard, press Command + V (Mac OS) or Ctrl + V (Windows/Linux) to paste the copied operator(s) to the Data Workflow canvas.
6. Click Save.
7. Save your module.

TIP  Did you know you can copy an operator and paste it into a different Data Workflow? You'll use the same steps outlined above.

Deleting an Aggregate Operator

Lastly, you can delete this operator from your Data Workflow canvas. You can also use these same steps to delete a connection between two operators.

1. Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2. Click the (Settings) button to open the Data Workflow canvas.
3. Click the operator you want to delete.
4. On your keyboard, press Delete.

NOTE  Once you delete this operator, make sure to connect your remaining operators. If your Data Workflow path doesn't end with an Output or Console operator, your Data Workflow won't work. So, once you delete an operator, make sure to update any remaining paths to end at an Output or Console operator.

5. Click Save.
6. Save your module.

Resources

Lab

You can view this complete use case here: https://training.unqork.io/#/form/5f6a653e19c4e6024ed1e613/edit.