What you ask is a new aggregate function in tAggregateRow (as far as I understand) that would be "first not null/empty". Please create a bugtracker for this :-)

Now I remind that when I coded tAggregateRow in late 2006, I had in project to let the user create a "personnal aggregate function" (and I wanted the same for tSortRow). I could be good to reactivate this project. Can you also create a feature request? (feature requests created by users have more weight)

emendez wrote:

The tricky point here is that there may be a different function for each field.

This is not a tricky point, this is the way it already works. The tricky point is to enable specific aggregate function.

Sorry to bring back an old topic, but this is the only forum post I could find that described my problem.

I had a similar need : ie a "custom aggregate" function for the tAggregateRow. I found out a neat way of doing this, so I'm sharing it here, this seems like a pretty standard thing you'd want to do with Talend.

Basically I had a input row like this :

id;info
-------
1;"a"
1;"b"
1;"c"
2;"a"
2;"d"

And my desired output was this :

id;infos
--------
1;"a:b:c"
2;"a:d"

I needed to aggregate the "info" data grouped by the "id" data, separated by the ":" character.

The solution I found looks like this :

input ----> tJavaRow1 ----> tAggregateRow ----> tJavaRow2

tJavaRow1 :

In this tJavaRow, I input data in the globalMap to achieve what I want :

String mapIndex = input_row.id.toString();

if (globalMap.get(mapIndex) == null)
    globalMap.put(mapIndex, input_row.info.toString());
else
    globalMap.put(mapIndex, globalMap.get(mapIndex).toString() + ":" + input_row.info.toString());    

output_row.id= input_row.id;

After this code I have in my global map the correct data for each id (ie each info is concateneted and separated by ":").

The tAggregateRow serves a dual purpose : i only fill in the "group by" part of the component, (no operations), so the lines are effectively grouped. Also, the tAggregateRow "breaks" the normal data flow of Talend (begin / main / end), so I can re-use another tJavaRow after it, and it will only start once all the lines from my first input have been processed by the first tJavaRow.

tJavaRow2 :

In this tJavaRow, I fetch what's in the globalMap for my id, and I output it to the output_row :

String mapIndex = input_row.id.toString();

output_row.id= input_row.id;
output_row.infos= globalMap.get(mapIndex).toString();

Using this technique i was able to concatenate strings, but really you can define any user fonction you want. (You could store integer values as an array in the globalMap, and then in the final tJavaRow you could perform operations on this array to emulate sum, max, min, avg, std, ...)

Are there any big drawbacks to using this technique ?

Why yes, yes I could have...

Heh.My job just got 2 components simpler !