+4Let's say I have a table like this:
AttributeAAttributeBsome valuesome valuesome value
some value
some value
some value
some value
And I want to summarize how many nulls are in each column (attribute) of a table like this:
AttributeA has 2 blanks / nulls.
AttributeB has 1 blanks / nulls.
What is the best way to count how many nulls are in a given column of a table?
Best answer by ciarab
@dmatranga one way would be to use the attributevalidator in 2016 to test for attributes that have a value. This will push all the records that fail your null test through the failed port. ( If you do not have 2016 or later you could use a tester to test your attribute is missing,null, empty)
If you then use the list exploder to split where it has nulls in more than one column, add a statistics calculator to count the number of failed messages
The Failed column will now give you a total count of Nulls for each, if you wanted to use a duplicate filter on the Failed column to remove the multiple records you will be left with a single record that contains the Failed Value and Validation Message you need.
hope that helps!
+9@dmatranga one way would be to use the attributevalidator in 2016 to test for attributes that have a value. This will push all the records that fail your null test through the failed port. ( If you do not have 2016 or later you could use a tester to test your attribute is missing,null, empty)
If you then use the list exploder to split where it has nulls in more than one column, add a statistics calculator to count the number of failed messages
The Failed column will now give you a total count of Nulls for each, if you wanted to use a duplicate filter on the Failed column to remove the multiple records you will be left with a single record that contains the Failed Value and Validation Message you need.
hope that helps!
+17Even simpler: Use the Tester to check if the attribute has a value and then use the StatisticsCalculator on the Failed port as mentioned by @ciarab.
+4@ciarab @erik_jan Thank you both for your replies, going to try this now! I haven't used either of those transformers before, so I'm looking forward to checking these out.
Currently on the latest version of 2016.1
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
