Highlighted
Five Stars

## How calculate a total

Hi all,

I have  2 tables "consultation" et "bénéficiaire" and i want count the number of consultations per beneficiary.

I tried with this function

(consultation.idBeneficiaire == beneficiaire.idBeneficiaire)? (NombreTotal = NombreTotal+1) : 0

but i receive a message of error in figure 2.

Thank you.

Thank you for all.

Accepted Solutions
Highlighted
Sixteen Stars

## Re: How calculate a total

Hi,

Else, here is a new proposal:

I have 2 tables:

- beneficiaire with an id and a name:

- consultation with an idbeneficiaire:

The objective is to get the number of consultations for a beneficiaire.

So, the 2 tables are joined using a tMap with beneficiaire as the main table and consultation as the lookup table.

Before to join these 2 tables, I use a tAggregateRow to count the number of consultation records per beneficiaire (see previous answer to see the configuration for this component).

Here is the tMap:

And finally, the result:

What else?

TRF

All Replies
Highlighted
Sixteen Stars

## Re: How calculate a total

tAggregateRow is here for that.
Try it on table consultation.

TRF
Highlighted
Sixteen Stars

## Re: How calculate a total

The pointer to the documentation in french https://help.talend.com/reader/tM3os~7PoPzBK28jtBNgCw/i_YvOl2oUaVpW1UTnJao_g
You need to go with the "count" function.
Let us know if you have some problem with it. Else you know how it works to close the case )

TRF
Highlighted
Five Stars

## Re: How calculate a total

Thank you.

else i want don't find a problem because i must finish my OlapCube before 00:00 (time of tunisia)

Thank you sir.

Highlighted
Sixteen Stars

## Re: How calculate a total

OK.

Suppose I have a schema with a single column called idBeneficiaire  with the following values:

100
150
100
200
210
150
150
999

I can use the tAggregateRow like this:

- 1rst the output schema must have 1 column for the idBeneficiaire value + 1 column for the count result (datatype integer):

Then the tAggregate component can configured like this:

The output column "idBeneficiaire" is populated from the same input column.

The output column "comptage" is the result of the count function applied on the column "idBeneficiaire".

When connected to a tLogRow the result looks like this:

Starting job test at 22:04 13/02/2018.

[statistics] connecting to socket on port 3474
[statistics] connected
.--------------+--------.
|      tLogRow_32       |
|=-------------+-------=|
|idBeneficiaire|comptage|
|=-------------+-------=|
|100           |2       |
|210           |1       |
|200           |1       |
|999           |1       |
|150           |3       |
'--------------+--------'

[statistics] disconnected
Job test ended at 22:04 13/02/2018. [exit code=0]

That's what you are looking for.

TRF
Highlighted
Five Stars

## Re: How calculate a total

Thank you sir, but it isn't what i search.

i must do a join between two tables (beneficiaire) and ( consultation)

beneficiaire : contains the informations about the beneficiaries and,

The "idbeneficiaire" is unique . one id per one bénéficiaire.

So , to calculate for any beneficiaries how much consultation he's made per year, i must make the join between the both tables.

And the task, is the number of occurrence of "idbeneficiaire" in "consultation" table

Furthermore, based on tAggregateRow, i can apply functions only on data from a single source table.

The rest of the task, is the total number of consultation per beneficiary and per YEAR (how extract from Date).

So, what do you think doing here ?

Highlighted
Sixteen Stars

## Re: How calculate a total

That's exactly what you get with tAggregateRow with the count function (number of consultations per beneficiaire).
However, if you need (want) to get some other informations from the consultation or the beneficiaire, you may have a join using a tMap and there you can count the number of consultations per beneficiaire using a sequence. For that, on the tMap output side, add a field called count or anything else (datatype Integer) and use the following to populate this field:
Numerique.sequence(row1.idBeneficiaire, 1)
This means you'll have a sequence for each unique idBeneficiaire which count the number of rows for the corresponding value.
So, 2 solutions for 1 question. That's where this tool is a great one.

TRF
Highlighted
Five Stars

## Re: How calculate a total

Hi TRF,

i try your solution, but i find an error.

i try with " Numerique.sequence(mapping.idBeneficiaire, 1) " in tMap and with secondly a simple mapping.

in the picture you find the all of détails.

Thank you

Sixteen Stars

## Re: How calculate a total

That's my mistake, change Numerique.sequence to Numeric.sequence.
I saw your design but don't understand why you still have tAggregateRow if you want to tMap with sequence.

TRF
Highlighted
Five Stars

## Re: How calculate a total

ok , I will try, stay here please

Highlighted
Five Stars

## Re: How calculate a total

I delete the "Annee" attribute,

and i delete the tAggregateRow, but i find the same error.

Highlighted
Sixteen Stars

## Re: How calculate a total

You can go to the source code to find the error.
However, reviewing the solution, if you want 1 record per idBeneficiaire, tAggregateRow is better. Using tMap with sequence you'll have n records with the NombreTotal field incremented from 1 to n. So you will have to sort the result then get the high value for each idBeneficiaire.
Not so easy. Definitively consider the tAggregateRow option.
It's time for me.
Get your next message in the morning.

TRF
Highlighted
Five Stars

## Re: How calculate a total

ok , Thank you.

and have a good evening.

Highlighted
Sixteen Stars

## Re: How calculate a total

Hi,

Else, here is a new proposal:

I have 2 tables:

- beneficiaire with an id and a name:

- consultation with an idbeneficiaire:

The objective is to get the number of consultations for a beneficiaire.

So, the 2 tables are joined using a tMap with beneficiaire as the main table and consultation as the lookup table.

Before to join these 2 tables, I use a tAggregateRow to count the number of consultation records per beneficiaire (see previous answer to see the configuration for this component).

Here is the tMap:

And finally, the result:

What else?

TRF
Highlighted
Five Stars

## Re: How calculate a total

Good Morning,

yes i found a solution as in picture.

and now i want load my data cube with tPaloOutputMulti.

i'll launch a new topic above.

thank you

## OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

## Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

## Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

## Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog