3 Replies Latest reply on Jan 3, 2019 1:33 PM by Ken Flerlage

# Calculated Field To Omit A Table Row

Hello,

I have a workbook where an individual could belong to zero out of five clubs, belong to one club only or belong to multiple clubs.

If that member belongs to zero clubs I still want to display that member entry and email address once. If the member belongs to multiple clubs I want to show name and email once but list ALL clubs. See below:

I have attached a workbook. Thanks in advance and Happy New Year.

• ###### 1. Re: Calculated Field To Omit A Table Row

Hi Greg,

You didn't attach a workbook. My initial thoughts are this could possibly be done with the Analysis > Aggregate Measures feature, however the reason why the blank rows are being generated is due to the email field, not the club name field. What do you want to do with the email addresses ?

HTH

Peter

• ###### 2. Re: Calculated Field To Omit A Table Row

My apologies. It is now attached. I found if I slide email on the column shelf to the left between "First Name" and "Club Name"it makes things a bit better. Ultimately I want to always show member and email and club name(s). If they belong to zero clubs show Club Name as blank only in that single instance. Thanks.

• ###### 3. Re: Calculated Field To Omit A Table Row

Create a calculated field that counts the number of clubs (using an LOD) for each person:

Club Count

// Count the number of clubs.

{FIXED [Last Name], [First Name]: COUNTD(IIF([Club Name]<>"None", [Club Name], NULL))}

Then create another calculated field as follows:

Include

// Filter out "None" for people with 1+ clubs.

IF [Club Count]=0 THEN

"Include"

ELSE

IF [Club Name]="None" THEN

"Exclude"

ELSE

"Include"

END

END

Then drag that field to filters and only include values of "Include".

See attached workbook.