Skip to main content

I'm using an Aggregator transformer to group by an attribute and concatenate many attribute accordingly. I'm having a problem with the attribute column containing missing or null values. The Aggregator will populate the attribute containing missing or null values with the separator character when it should stay empty or null. Is this a bug? I've replicated the problem in the screenshot below

Hi @spatial_aus It's not a bug - it's correctly returning a comma-separated list of empty values. You could remove the separator to get what you expect for 'txt'. What would you expect if the separator is a comma and just one of three features has a value for 'txt'? e.g. ",,b" or just "b"?


If you want to concatenate whilst dropping the null and missing values you can build a list in the aggregator then use the list concatenator which has an option to drop empty values


Hi @spatial_aus It's not a bug - it's correctly returning a comma-separated list of empty values. You could remove the separator to get what you expect for 'txt'. What would you expect if the separator is a comma and just one of three features has a value for 'txt'? e.g. ",,b" or just "b"?

Hi @danatsafe, thanks for your response. I wouldn't say that it is returning a list (or what is termed a list in FME speak e.g. list{}) but rather an attribute value.

I need to keep the separator in as I may need to aggregate this column if new data is populated in it in the future.

I would expect to get "b" not ",,b". I don't see ",,b" as a logical way to concatenate values. I would argue (unless convinced otherwise) that this is against the concept of concatenating. If the value is missing/empty/null it is non-existent, it shouldn't exist and should not be included in some sort of list creation when concatenating values. Two nothings concatenated goes against the definition of concatenation. Concatenation is the linking together of "things" not "nothings".

Returning a value of ",,b" would imply that the missing/null/empty values are not actually missing/null/empty but rather a something.

 

The sum and average accumulation within the aggregator ignores the nulls so why shouldn't the concatenate?


If you want to concatenate whilst dropping the null and missing values you can build a list in the aggregator then use the list concatenator which has an option to drop empty values

Thanks @egomm, your solution is feasible for 1 or 2 attributes but may get cumbersome when wanting to concatenate many attributes.

I reckon it would be handy to have this option (drop/ignore empty values) back in the Aggregator transformer to avoid the amount of downstream transformers e.g. numerous ListConcatenator, ListRemovers etc.


Thanks @egomm, your solution is feasible for 1 or 2 attributes but may get cumbersome when wanting to concatenate many attributes.

I reckon it would be handy to have this option (drop/ignore empty values) back in the Aggregator transformer to avoid the amount of downstream transformers e.g. numerous ListConcatenator, ListRemovers etc.

@spatial_aus It makes sense to have the option to Drop Empty and Null Elements on the Aggregator as it is on the ListConcatenator. Please add this idea to https://knowledge.safe.com/content/idea/list.html


Hi, 

I ran into the same issue and the idea mentioned by @fmelizard seems to no longer exist. I came with the following somewhat funky solution:

I make an attribute for the number of aggregated features and then I add an attribute manager with a conditional expression:

 

This works for every feature where ALL aggregated values are empty. So you still might need to replace e.g. ‘||’ with ‘|’ for features where some attributes have a value and some don’t. Also, if you need to do this for a lot of attributes, you might use an attribute exploder after the aggregator to do just one conditoinal for all attributes. As I said, it’s a bit funky, but seems to be the best solution in my case.


To handle null/missing values in a concatenation, you can use the following expression in an AttributeCreator/Manager.

@ReplaceRegularExpression(@ReplaceRegularExpression(@Value(att),"(^,+|,+$)","",caseSensitive=TRUE),",+",",",caseSensitive=TRUE)

If your delimiter is not a comma, then you would replace the highlighted portion with your delimiter.

 


Reply