Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.
Originally posted by: ryehThis request came in from a customer - how do you count the number of non-blanks or non-nulls in your data. So for data like:
Field1, Field2, Field3
1000, , 3000
2000, 2200 , 3000
3000, ,
We want:
Field1 - 3
Field2 - 1
Field3 - 2
1. The customer initially wanted to do this across all fields, which led us to using the third pin of the Data Analyzer node (gives the frequency count of each field). This method works, but it takes a bit of work to extract the info (the result is presented in column pairs). With some pivoting, filtering, and aggregating, we can get the result. The problem is that the customer could potentially have many unique values (up to 1M) for each column. The Data Analyzer keeps 1000 distinct values by default. It can be bumped up, but for scaling purposes, we didn't think it would be a good idea.
2. So we decided to take advantage of the Histogram node. We can count up all the occurrences of the distinct values, and add up up the non-blank and non-null values. We created a library node for this, and would use one instance for each field that we wanted to analyze. The customer said it wasn't critical to count up all the fields, so it was sufficient to specify a handful of fields, run it through the library nodes, and then Cat the results. (By the way, we could have cycled through all the fields with the Do While nodes.)
3. The previous approach works, but can be a bit inefficient since we don't actually care what the unique values are, just the number of times they occur collectively. So we decided to do a little bit of scripting to go at the problem more directly. Like #2, we still specify the fields we want to count, but it can be done in a parameter, so no need to use a separate node for each field. The Agg Ex node is used to keep track of the fields we want to count, and then increment the count for each non-blank/non-null value. On the last record, we dump out the results.
Attachments:
CountNonBlanks.brg