How to count the number of occurences of a column?

I am pretty new to Webi and am having an issue creating a variable. I'm trying to check if there is more than 1 email address for each entity legacy account number and if 1 of the contact names contains "Annual Report". So when I flag each entity legacy account number for no email only the ones without a contact name that contains "Annual Report" will be pulled.

In the example above only the yellow groups should be called no email. Right now all of them are being pulled into no email. I have tried using if and match as those are what I am most familiar with.

Any suggestions?

There are number of ways you could do this. I am going to give an example using two variables, but you could easily combine them into one.

Has No Email Var=If(Match(Upper([Contact EmailAddress]); "NOEMAIL*"); 1; 0)
Annual Report Contact Name Var=If(Match(Upper([Contact Name]); "ANNUAL REPORT*"); 1; 0)

Then you would apply a report filter with two components...

Has No Email Var = 1 
AND
Annual Report Contact Name Var = 0

Let me explain a few things...

• The purpose of the Upper function is the Match function is case sensitive. If you know your email address are always lower case then you could remove that the Upper function and have it match on "noemail*".
• It is significant that I only have a asterisk ("*") at the end of the string being sought. That will only find a match where the corresponding column value starts with that string. If you want it to be true whenever the string is found anywhere in the column being searched you would be asterisks on both ends.

• You could also put limiting criteria in your query filter. But here is where thing can get confusing. Within the query filter you can choose the Matches pattern operator. However, the wildcard character is different ("%" rather than "*") and you do not put double-quotes around your search text. So you would have some thing like this...

  Contact EmailAddress Matches pattern noemail%

  AND

  Contact Name Different from pattern Annual Report%

  I am sure you noticed I didn't convert the search text to uppercase. In the Query Panel Web Intelligence is case-insensitive and would likely follow the case-sensitivity of the database of the source data. All of our databases are case-insensitive so if yours is case-sensitive you may need to play around this this a bit. Or just go with the approach of creating the variables and report filters as I initially laid out.

• If you want a wildcard for a single character rather than multiple characters (which is what "*" and "%" will do) you need to use a "?" within your variable definition or a "_" in your query filter.

Hope this helps,

Noel