How to use IA function: Count(field)

infodfs · Post by **infodfs** » Wed Jan 29, 2014 10:49 am

Hi,
I create a data rule to count distinct value in a column (data type is NUMBER) as below
COUNT(CUST_SCORE) = 9999
The Output Records was "Meet Rule Conditions". The result returns Not Meet 100%, but Oracle DB shows there are at least 6400 cust_score = 9999.
I've also checked the not meet returns, but the returned value was not correct either. Could anyone tell me how to use COUNT function correctly?

ray.wurlod · Post by **ray.wurlod** » Wed Jan 29, 2014 3:16 pm

You're setting a rule that requires the count itself to be 9999.

It's not totally clear what you actually want; is it that the count of rows in which CUST_SCORE is 9999 to exceed a certain number/percentage?

vmcburney · Post by **vmcburney** » Wed Jan 29, 2014 6:31 pm

Call me crazy but wont the count of CUST_SCORE always be 1? Information Analyzer runs a rule that evaluates the data one row at a time. You need to rethink what you want this rule to do. If you are looking for a thresh hold of records where CUST_SCORE = 9999 then you set the rule to be CUST_SCORE = 9999 and you use the rule thresh hold and metric to check the overall and percentage of failure.

infodfs · Post by **infodfs** » Thu Jan 30, 2014 7:05 am

Sorry for unclear statement in my question. I want to use the rule to count total record of a column if the record's value equals 9999. Rule logic is as below.
Source Data: Count(CUST_SCORE)
Type of Check: =
Reference Data: 9999
Here is the description of this function from IA User guide:
count(column) - An aggregate function that provides a count of the number of distinct values in the whole column
Did I miss anything in my rule logic? please help.

stuartjvnorton · Post by **stuartjvnorton** » Thu Jan 30, 2014 5:25 pm

So you want the to be 9999 distinct values in the column, yes?

If you are trying to use it to check the row count for the table, COUNT would only work to check row count if the column is both 100% populated and 100% unique.