How calculate a total

[Anonymous]

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.

[TRF]

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

[TRF]

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 )

[Anonymous]

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.

[TRF]

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.

[Anonymous]

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 ?

[TRF]

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.

[Anonymous]

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

[TRF]

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.

[Anonymous]

ok , I will try, stay here please