Skip to main content
Question

Count for each row the cells that contain values

  • January 30, 2020
  • 6 replies
  • 230 views

Forum|alt.badge.img

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.15

 

1.2

 

2

 

0.11

 

 

1.330.20.130.41.52.1

 

Can anyone help me to get the below outcome?

DBKCount132235

 

Thanks in advance.

6 replies

oscard
Influencer
Forum|alt.badge.img+21
  • Influencer
  • January 30, 2020

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.

 


ebygomm
Influencer
Forum|alt.badge.img+32
  • Influencer
  • January 30, 2020

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

 


takashi
Influencer
  • January 30, 2020

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.


Forum|alt.badge.img
  • Author
  • January 30, 2020
ebygomm wrote:

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!


Forum|alt.badge.img
  • Author
  • January 30, 2020
takashi wrote:

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!


Forum|alt.badge.img
  • Author
  • January 30, 2020
oscard wrote:

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!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings