Excel file : average on dynamic data

[Oli76]

Hi,

I’m a beginner with Talend and I can’t figure out how to achieve what I need :

I’ve an Excel file and I need to do the average of all columns for a reference.

The issue is that the reference is only on the last row of its subset and the number of rows per reference is variable (from one row to more than 10).

Example of simplified file input file :

Ref | temp 1 | temp 2 | temp 3 | Press 1

A | 25 | 26 | 80 | 1.5

   | 30 | 32 | 85 | 1.8

B | 32 | 30 | 82 | 2.1

   | 30 | 32 | 85 | 1.8

   | 31 | 30 | 82 | 2.1

   | 32 | 29 | 79 | 1.9

C | 29 | 33 | 80 | 2.0

1 row for reference A, two rows for reference B and 4 rows for reference C.

Required output file is : 

Ref | temp 1 | temp 2 | temp 3 | Press 1

A | 25 | 26 | 80 | 1.5

B | 31 | 31 | 83 | 1.7

C | 31 | 31 | 82 | 2.0

Thanks for your help.

Regards,

[Anonymous]

Hi
The trick to accomplish this requirement is adding a sequence id for each row and sort the rows order by sequence id, fill in the null value with last non-null value in Ref column, finally, do the average of all columns on tAggregateRow component. See images.

Code on tJavaRow:

output_row.id = input_row.id;

output_row.temp1 = input_row.temp1;
output_row.temp2 = input_row.temp2;

if(input_row.Ref==null){
output_row.Ref=(String)globalMap.get("key");
}else{
output_row.Ref=input_row.Ref;
globalMap.put("key",input_row.Ref);
}

Regards

Shong

[Oli76 · Accepted solution]

Hi Shong,

Thanks for the id column tips.

I was looking how to sort my file in decreasing order but can't figure out how to do it.

I'll do what you propose.

Thanks.