topic How calculate a total in Talend Studio

How calculate a total

Anonymous — Tue, 13 Feb 2018 19:25:49 GMT

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.

I hope receive your help for resolve my problem.

Thank you.

Thank you for all.

Re: How calculate a total

TRF — Tue, 13 Feb 2018 19:35:46 GMT

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

Re: How calculate a total

TRF — Tue, 13 Feb 2018 20:33:40 GMT

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 )

Re: How calculate a total

Anonymous — Tue, 13 Feb 2018 20:40:04 GMT

Thank you.

i'll read it

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

Thank you sir.

Re: How calculate a total

TRF — Tue, 13 Feb 2018 21:05:47 GMT

OK.

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

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.

Re: How calculate a total

Anonymous — Tue, 13 Feb 2018 21:21:38 GMT

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,

consultation: contains the informations about the consultations made.

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 ?

Re: How calculate a total

TRF — Tue, 13 Feb 2018 21:54:12 GMT

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.

Re: How calculate a total

Anonymous — Tue, 13 Feb 2018 23:04:41 GMT

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

aggregate.png
job.png
SEQUENCE.png
tmap.png

Re: How calculate a total

TRF — Tue, 13 Feb 2018 23:10:14 GMT

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.

Re: How calculate a total

Anonymous — Tue, 13 Feb 2018 23:14:25 GMT

ok , I will try, stay here please

Re: How calculate a total

Anonymous — Tue, 13 Feb 2018 23:21:57 GMT

I delete the "Annee" attribute,

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

job sans aggregate.png
sequence cons.png

Re: How calculate a total

TRF — Tue, 13 Feb 2018 23:32:55 GMT

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.

Re: How calculate a total

Anonymous — Tue, 13 Feb 2018 23:36:41 GMT

ok , Thank you.

and have a good evening.

Re: How calculate a total

TRF — Wed, 14 Feb 2018 08:39:13 GMT

Hi,

Did you get your solution?

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?

Re: How calculate a total

Anonymous — Wed, 14 Feb 2018 08:57:42 GMT

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

sortietotalparAnnee.png