Calculated Field Perform Calc on field - on count if the value is one of...

Explorer ,
Jan 28, 2015 Jan 28, 2015

Copy link to clipboard

Copied

I am trying to add how many records have the Ethnic value of "P", "U", or "W". I have tried different variations of:

iif (query.ethnic_cod = ["P", "U", "W"], 0, 1) and the report errors out.

Please advise.

TOPICS
Reporting

Views

715

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Advocate , Feb 02, 2015 Feb 02, 2015
Ah, that explains it. If the field is defined as char(2) then single letter codes will have a trailing space.Try the following:iif (FindNoCase(trim(query.ethnic_cod), "PUW") eq 0, 0, 1)The trim() function will remove a leading or trailing space.It is important to understand that this solution still has the problem that if you have a code of "PU" or "UW" then the statement will return 0. Which is not what you want.If you can confirm that the ethnic_cod field has a space after the "P" or "U" or "W...

Likes

Translate

Translate
Explorer ,
Jan 29, 2015 Jan 29, 2015

Copy link to clipboard

Copied

How do you code the expression when you are looking for the value from the query to be one of a list of values?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 29, 2015 Jan 29, 2015

Copy link to clipboard

Copied

Use the following:

iif (FindNoCase(query.ethnic_cod, "PUW") eq 0, 0, 1)

This will return 0 if the ethnic_code is "P" or "U" or "W", otherwise it will return 1. Switch the 0 and 1 to get the opposite result, which I suspect is what you really want.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 30, 2015 Jan 30, 2015

Copy link to clipboard

Copied

I am trying to count the ethnic codes that are not "P", "U", "A". This code

gives me a value of zero (there are 13). If I switch the 0 and 1 I get a

value or 157 (total of all the records).

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Thu, Jan 29, 2015 at 12:32 PM, Eddie Lotter <forums_noreply@adobe.com>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Feb 02, 2015 Feb 02, 2015

Copy link to clipboard

Copied

Cozmo2 wrote:

This code gives me a value of zero (there are 13).

The FindNoCase() function simply searches for one string within another.

If your ethnic_cod field is more than one character wide then this approach will not work.

Is the ethnic_cod field exactly one character wide?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 02, 2015 Feb 02, 2015

Copy link to clipboard

Copied

Yes, it is 2 characters wide.

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Mon, Feb 2, 2015 at 10:39 AM, Eddie Lotter <forums_noreply@adobe.com>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Feb 02, 2015 Feb 02, 2015

Copy link to clipboard

Copied

Ah, that explains it. If the field is defined as char(2) then single letter codes will have a trailing space.

Try the following:

iif (FindNoCase(trim(query.ethnic_cod), "PUW") eq 0, 0, 1)

The trim() function will remove a leading or trailing space.

It is important to understand that this solution still has the problem that if you have a code of "PU" or "UW" then the statement will return 0. Which is not what you want.

If you can confirm that the ethnic_cod field has a space after the "P" or "U" or "W" then a safer algorithm would be the following:

iif(FindNoCase(query.ethnic_cod, "P ,U ,W ") eq 0, 0, 1)

Instead of stripping the space from the field I have added the space after each letter in the second string of the FindNoCase() function. I am assuming your ethnic_cod field does not use the comma character, as I'm using it as a delimiter to prevent a match with a leading space.

I am also assuming the code is not case sensitive. If it is then use the Find() function rather than the FindNoCase() function.

Do you understand the logic behind what I am showing you?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 02, 2015 Feb 02, 2015

Copy link to clipboard

Copied

Thank you for the help and the explanation.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Feb 02, 2015 Feb 02, 2015

Copy link to clipboard

Copied

You're welcome Bonni.

What solution did you end up using?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 02, 2015 Feb 02, 2015

Copy link to clipboard

Copied

LATEST

The one you said was the correct way. It made sense!

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Mon, Feb 2, 2015 at 3:21 PM, Eddie Lotter <forums_noreply@adobe.com>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines