Hello,
I would like to count the amount of cells that contain a value for each row in my dataset. It is basically the COUNTA function of Excel. But I don't know how to apply it in FME.
I have a large table with 100+ columns which looks like this:
DBKAlCaClPbZn10.10.151.2
2
0.11
1.330.20.130.41.52.1
Can anyone help me to get the below outcome?
DBKCount132235
Thanks in advance.
Maybe you could use a NullAttributeMapper with a Regex to detect any value except null or empty. If it is detected, its value could be mapped to "1".
This regex could work:
[\\s\\S]+
But you need to trimm your values first in case they could contain just blank spaces.
EDIT: The StatiscCalculator doesn't work for this, sorry.
Another solution would be adding a second NullAttrubuteMapper after the first one to map null, empty or missing values to 0. Then use a ExpressionEvaluator to make the sum.
It's not exactly what it's designed for, but you can use the AttributeValidator to validate that attribute have a length of 0, this then creates a list for each attribute where the value exists, you can then use a ListElementCounter to find the length of the list which equates to the number of fields with values
Another thought. Use the AttributeListPopulator from Hub (Set Yes to the Drop Empty Parts parameter) to populate all attribute values except DBK into a list attribute, then count the number of elements with the ListElementCounter.
It's not exactly what it's designed for, but you can use the AttributeValidator to validate that attribute have a length of 0, this then creates a list for each attribute where the value exists, you can then use a ListElementCounter to find the length of the list which equates to the number of fields with values
Thanks ebygomm. This indeed works!
Another thought. Use the AttributeListPopulator from Hub (Set Yes to the Drop Empty Parts parameter) to populate all attribute values except DBK into a list attribute, then count the number of elements with the ListElementCounter.
Thank you takashi, this works too!
Maybe you could use a NullAttributeMapper with a Regex to detect any value except null or empty. If it is detected, its value could be mapped to "1".
This regex could work:
[\\s\\S]+
But you need to trimm your values first in case they could contain just blank spaces.
EDIT: The StatiscCalculator doesn't work for this, sorry.
Another solution would be adding a second NullAttrubuteMapper after the first one to map null, empty or missing values to 0. Then use a ExpressionEvaluator to make the sum.
This indeed also works. But I have more than 100 columns I want to check. So setting up the ExpressionEvaluator will take some time in comparison with the above mentioned methods. Thanks though oscard!