topic Excel file : average on dynamic data in Talend Studio

Excel file : average on dynamic data

Oli76 — Tue, 17 Mar 2020 07:57:47 GMT

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,

Re: Excel file : average on dynamic data

Anonymous — Fri, 20 Mar 2020 08:20:03 GMT

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

Re: Excel file : average on dynamic data

Oli76 — Fri, 20 Mar 2020 10:59:05 GMT

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.