How calculate a total

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.

 I hope receive your help for resolve my problem.

 

Thank you. 

 

Thank you for all.


Accepted Solutions
Forteen Stars TRF
Forteen Stars

Re: How calculate a total

Hi,

Did you get your solution?

Else, here is a new proposal:

Capture.PNG

 

I have 2 tables:

- beneficiaire with an id and a name:

Capture.PNG

- consultation with an idbeneficiaire:

Capture.PNG

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:

Capture.PNG

And finally, the result:

Capture.PNG

What else?


TRF

All Replies
Forteen Stars TRF
Forteen Stars

Re: How calculate a total

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

TRF
Forteen Stars TRF
Forteen Stars

Re: How calculate a total

The pointer to the documentation in french Smiley Wink 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 Smiley Wink)

TRF
Five Stars

Re: How calculate a total

Thank you.

i'll read it Smiley Happy

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

 

Thank you sir.

Forteen Stars TRF
Forteen 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):

Capture.PNG

Then the tAggregate component can configured like this:

Capture.PNG

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
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,

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). Smiley Happy

So, what do you think doing here ?

Forteen Stars TRF
Forteen 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
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

 

Forteen Stars TRF
Forteen 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
Five Stars

Re: How calculate a total

ok , I will try, stay here please

Five Stars

Re: How calculate a total

I delete the "Annee" attribute, 

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

Forteen Stars TRF
Forteen 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
Five Stars

Re: How calculate a total

ok , Thank you. 

 

and have a good evening.

Forteen Stars TRF
Forteen Stars

Re: How calculate a total

Hi,

Did you get your solution?

Else, here is a new proposal:

Capture.PNG

 

I have 2 tables:

- beneficiaire with an id and a name:

Capture.PNG

- consultation with an idbeneficiaire:

Capture.PNG

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:

Capture.PNG

And finally, the result:

Capture.PNG

What else?


TRF
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