July 30, 2023

tAggregateSortedRow – Docs for ESB 7.x

  2. Talend for ESB Docs
  3. Docs 7.x
  4. tAggregateSortedRow – Docs for ESB 7.x

tAggregateSortedRow

Aggregates the sorted input data for output column based on a set of operations. Each
output column is configured with many rows as required, the operations to be carried out and
the input column from which the data will be taken for better data aggregation.

tAggregateSortedRow Standard properties

These properties are used to configure tAggregateSortedRow running in the Standard Job framework.

The Standard
tAggregateSortedRow component belongs to the Processing family.

The component in this framework is available in all Talend
products.

Basic settings

Schema and Edit
Schema

A schema is a row description, it defines the number of fields to
be processed and passed on to the next component. The schema is
either Built-in or stored remotely
in the Repository.

Click Edit
schema to make changes to the schema. If the current schema is of the Repository type, three options are available:

  • View schema: choose this
    option to view the schema only.

  • Change to built-in property:
    choose this option to change the schema to Built-in for local changes.

  • Update repository connection:
    choose this option to change the schema stored in the repository and decide whether
    to propagate the changes to all the Jobs upon completion. If you just want to
    propagate the changes to the current Job, you can select No upon completion and choose this schema metadata
    again in the Repository Content
    window.

Click Sync columns to retrieve
the schema from the previous component connected in the Job.

This
component offers the advantage of the dynamic schema feature. This allows you to
retrieve unknown columns from source files or to copy batches of columns from a source
without mapping each column individually. For further information about dynamic schemas,
see
Talend Studio

User Guide.

This
dynamic schema feature is designed for the purpose of retrieving unknown columns of a
table and is recommended to be used for this purpose only; it is not recommended for the
use of creating tables.

 

Built-in: The schema will be
created and stored locally for this component only. Related topic:
see
Talend Studio User Guide.

 

Repository: The schema already
exists and is stored in the Repository, hence can be reused in
various projects and Job flowcharts. Related topic: see

Talend Studio User
Guide.

Input rows count

Specify the number of rows that are sent to the tAggregateSortedRow component.

Note:

If you specified a Limit for
the number of rows to be processed in the input component, you
will have to use that same limit in the Input rows count field.

Group by

Define the aggregation sets, the values of which will be used for
calculations.

 

Output Column: Select the column
label in the list offered based on the schema structure you defined.
You can add as many output columns as you wish to make more precise
aggregations.

Ex: Select Country to calculate an average of values for each
country of a list or select Country and Region if you want to
compare one country’s regions with another country’ regions.

 

Input Column: Match the input
column label with your output columns, in case the output label of
the aggregation set needs to be different.

Operations

Select the type of operation along with the value to use for the
calculation and the output field.

 

Output Column: Select the
destination field in the list.
 

Function: Select the operator among:

  • count: calculates the number of rows

  • min: selects the minimum value

  • max: selects the maximum value

  • avg: calculates the average

  • sum: calculates the sum

  • first: returns the first value

  • last: returns the last value

  • list: lists values of an aggregation by multiple keys.

  • list (object): lists Java values of an aggregation by multiple keys

  • count (distinct): counts the number of the distinct rows

  • standard deviation: calculates the
    variability of a set of value.

  • union (geometry): makes the union of a set of Geometry objects

  • population standard deviation: calculates the
    spread of a data distribution. Use this function if the data to be
    calculated is considered a population on its own. This calculation
    supports 39 decimal places.
  • sample
    standard deviation    : calculates the spread of a data
    distribution. Use this function if the data to be calculated is
    considered a sample from a larger population. This calculation
    supports 39 decimal places.

 

Input column: Select the input
column from which the values are taken to be aggregated.

 

Ignore null values: Select the
check boxes corresponding to the names of the columns for which you
want the NULL value to be ignored.

Advanced settings

tStatCatcher Statistics

Check this box to collect the log data at component level.

Global Variables

Global Variables

ERROR_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error check box is
cleared, if the component has this check box.

NB_LINE: the number of rows read by an input component or
transferred to an output component. This is an After variable and it returns an
integer.

A Flow variable functions during the execution of a component while an After variable
functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl +
Space to access the variable list and choose the variable to use from it.

For further information about variables, see
Talend Studio

User Guide.

Usage

Usage rule

This component handles flow of data therefore it requires input
and output, hence is defined as an intermediary step.

Sorting and aggregating the input data

This scenario describes a Job that sorts the entries of the input data based on two
columns and displays the sorted data on the console, then aggregates the sorted data based
on one column and displays the aggregated data on the console.

tAggregateSortedRow_1.png

Adding and linking components

  1. Create a new Job and add the following components by typing their names in the
    design workspace or dropping them from the Palette: a tFixedFlowInput
    component, a tSortRow component, a tAggregateSortedRow component, and two tLogRow components.
  2. Link tFixedFlowInput to tSortRow using a Row >
    Main connection.
  3. Do the same to link tSortRow to the first
    tLogRow, link the first tLogRow to tAggregateSortedRow, and link tAggregateSortedRow to the second tLogRow.

Configuring the components

Sorting the input data

  1. Double-click tFixedFlowInput to open its
    Basic settings view.

    tAggregateSortedRow_2.png

  2. Click the […] button next to Edit schema and in the pop-up window define the
    schema by adding four columns: Id and
    Age of Integer type, and Name and Team of
    String type.

    tAggregateSortedRow_3.png

    Click OK to close the schema editor and
    accept the propagation prompted by the pop-up dialog box.
  3. In the Mode area, select Use Inline Content(delimited file), and then in the
    Content field displayed, enter the input
    data to be sorted and aggregated. In this example, the input data is as
    follows:

  4. Double-click tSortRow to open its Basic settings view.

    tAggregateSortedRow_4.png

  5. Click the [+] button below the Criteria table to add as many rows as required and
    then specify the sorting criteria in the table. In this example, two rows are
    added, and the input entries will be sorted based on the column Team and then the column Age, both in ascending order.
  6. Double-click the first tLogRow to open its
    Basic settings view.

    tAggregateSortedRow_5.png

  7. In the Mode area, select Table (print values in cells of a table) for better
    readability of the sorting result.

Aggregating the sorted data

  1. Double-click tAggregateSortedRow to open its
    Basic settings view.

    tAggregateSortedRow_6.png

  2. Click the […] button next to Edit schema and in the pop-up window define the
    schema by adding five columns: AggTeam of
    String type, AggCount, MinAge, MaxAge,
    and AvgAge of Integer type.

    tAggregateSortedRow_7.png

    Click OK to close the schema editor and
    accept the propagation prompted by the pop-up dialog box.
  3. In the Input rows count field, enter the
    exact number of rows of the input data. In this example, it is 8.
  4. Click the [+] button below the Group by table to add as many rows as required and
    specify the aggregation set in the table. In this example, the data will be
    aggregated based on the input column Team.
  5. Click the [+] button below the Operations table to add as many rows as required and
    specify the operation to be carried out and the corresponding input column from
    which the data will be taken for each output column. In this example, we want to
    calculate the number of the input entries, the minimum age, the maximum age, and
    the average age for each team.
  6. Double-click the second tLogRow to open its
    Basic settings view.

    tAggregateSortedRow_8.png

  7. In the Mode area, select Table (print values in cells of a table) for better
    readability of the sorting result.

Saving and executing the Job

  1. Press Ctrl + S to save the Job.
  2. Execute the Job by pressing F6 or clicking
    Run on the Run tab.

    tAggregateSortedRow_9.png

    As shown above, the input entries are sorted based on the column Team and then the column Age, both in ascending order, and the sorted entries are then
    aggregated based on the column Team.
Document get from Talend https://help.talend.com
Thank you for watching.
Docs 7.x
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x