"Best Way" to Filter 3 Fields Where 2 or 3 of Them Have a Same Entry

[Cognos_Jan2017]

For simplicity, I will call the 3 Fields of Interest (with sample data) ...
Primary    Secondary    Tertiary
A                 A                B ---> This is what I seek, where 2 Fields have the same entry
A                 B                C ---> NOT interested in this row
B                 B                B ---> This is what I seek, where 3 Fields have the same entry

I can write a new Data Item with Case Whens to calculate 2 or 3 when 2 or more Fields are the same.

I can add that new Data Item to a List, then Filter to see which records have a 2 or 3.  We will be
searching thousands of records for a very few where Primary    Secondary    Tertiary have 2 or 3
with the same entry.

Thoughts please.  TIA, Bob

[bus_pass_man]

If you want to filter without creating a calculation you ought to try a filter expression

Primary = secondary
or
Primary = tertiary
or
secondary = tertiary


that alone ought to cover the case where primary = secondary and primary = teriary but it shouldn't be too hard to add that as an additional or with brackets to set the bounds of the and.

[Cognos_Jan2017]

Thank you bus_pass_man.

It should work, but perhaps because of another issue it did not work.

Please see attached Excel file.  Thank you.
=====================================================
Our Vendor DB uses "Structured Data Name" and "Structured Data Value" in a Table.

We can display Fields of Primary, Secondary, and Tertiary by making Data Items such as ...
Data Item = "ThePrimary"
Case [Structured Data Name]
When 'Primary' Then [Structured Data Value]
End

Data Item = "TheSecondary"
Case [Structured Data Name]
When 'Secondary' Then [Structured Data Value]
End

Data Item = "TheTertiary"
Case [Structured Data Name]
When 'Tertiary' Then [Structured Data Value]
End

But we are unable to Place those three entries of ThePrimary, TheSecondary, and TheTertiary on one row where we have
other Fields to the Left to identify other Data for Groupings.

We have another Report with 3 Structured Data Names where they DO appear on each row.  I could not "detect" why that worked.

I tried cutting, (but NOT deleting from Source Query), [Structured Data Name] and [Structured Data Value]
hoping that would work to place Primary - Secondary - Tertiary on one row, but it didn't.

Thought of a repeater but that would only concatenate Primary  Secondary --- Tertiary which would not
work like your filter recommendation would.

Further Thoughts and discussion?  Thank you, Bob

[Cognos_Jan2017]

Here is the attached Excel file example.

[seb24c]

Wrap each of ThePrimary, TheSecondary, TheTertiary in a: maximum ( XXX for [Unique ID] ). Then the filters bus_pass_man provided should work.

[Cognos_Jan2017]

Thank you seb24c.

I added a Detail Filter of ...
[ThePrimary] = Maximum([ThePrimary] for [Unique ID])
Adding just the above does produce ONE row in the List.

Trying another Detail Filter of ...
[TheSecondary] = Maximum([TheSecondary] for [Unique ID])
Results in No Data Available as that would be like an "And".

How would I wrap the 3 Fields to see one row for each [Unique ID] like ...
[Unique ID]     [ThePrimary]     [TheSecondary]     [TheTertiary]
   123                     A                        B                        C
   456                     A                        A                        C
   789                     A                        A                        A
   246                     B                        B                        C
   357                     B                        B                        B

So the above would be ready for bus_Pass_man's recommended Filter?

TIA, Bob

[seb24c]

They should be data items, not detail filters:

ThePrimary = maximum ( case [Structured Data Name] when 'Primary' then [Structured Data Value] end for [Unique ID] )

The detail filter would then use those three data items: [ThePrimary] = [TheSecondary] or [ThePrimary] = [TheTertiary] or [TheSecondary] = [TheTertiary]

[Cognos_Jan2017]

Thank you seb24c.

I have added your recommendations.  Everything Validated.

Trying to run the Report, so far, is taking a very long time, and it has not opened yet.

I will continue testing this, and be in touch.

Thanks again, Bob

[Cognos_Jan2017]

seb24c ...

The Report ran and placed all 3 Structured Data Values on one row, but all the Structured Data Values
are 0 which means somehow it may have calculated (and reported as) Booleans for the Detail Filter.

I will temporarily remove the Detail Filter and see what the Report says.

Am continuing and will keep in touch.

Thank you, Bob

[seb24c]

I mocked up a data set and tried it myself. See attached.

My data items are:
c_Primary: maximum ( if ( [Structured Data Name] = 'Primary' ) then ( [Structured Data Value] ) else ( null ) for [ID] )
c_Secondary: maximum ( if ( [Structured Data Name] = 'Secondary' ) then ( [Structured Data Value] ) else ( null ) for [ID] )
c_Tertiary: maximum ( if ( [Structured Data Name] = 'Tertiary' ) then ( [Structured Data Value] ) else ( null ) for [ID] )

(Case should work fine I just prefer if/then.)

My detail filter is:
[c_Primary] = [c_Secondary] or [c_Primary] = [c_Tertiary] or [c_Secondary] = [c_Tertiary]

I believe it's working as you need it to, based on the output I get.

[Cognos_Jan2017]

Update ...

First, I must admit that the Detail Filter you recommended was accidentally entered by me in Summary Filter in the
Cognos 10.2.2 I am using (I posted this in CA 11 because we should be upgrading to CA 11.0.x or CA 11.1.x soon).

The Summary Filter did report the Primary, Secondary, and Tertiary on one row (all values as 0), plus all [Unique IDs] were there.

Then I changed the Filter to your recommended Detail Filter, and the report said No Data Available.

Removing the Detail Filter resulted in ...
The Primary, Secondary, and tertiary on one row (all values as 0), plus all [Unique IDs] were there.

I then tried a very simple Detail Filter of ... [ThePrimary] = [TheSecondary] but that resulted in No Data Available.

These all validate, but result in 0 with No Detail Filter ...
maximum (case [Structured Data Name] when 'Primary' then [Structured Data Value] end for [Unique ID])

maximum (case [Structured Data Name] when 'Secondary' then [Structured Data Value] end for [Unique ID])

maximum (case [Structured Data Name] when 'Tertiary' then [Structured Data Value] end for [Unique ID])

Current attempts at Detail Filter for the above result in No Data Available.

Actually, all this is good because I (cognoise) readers can learn from this.

Continuing discussion welcomed.

TIA, Bob

[Cognos_Jan2017]

Update, Monday afternoon, 17 Feb 2020.

Running this in 10.2.2 now.  Was able, by setting "Aggregate Function" to Maximum, to get the
Primary, Secondary, and Tertiary entries all on one row for all the Unique IDs.  To do that, I defined
the Data Items as ...

Case [Structured Data Name]
When 'Primary' Then [Structured Data Value]
End

Case [Structured Data Name]
When 'Secondary' Then [Structured Data Value]
End

Case [Structured Data Name]
When 'Tertiary' Then [Structured Data Value]
End

However, was unable to write a Filter that works to see rows
where the same entry is reported 2 or 3 times.
This Filter results in "No Data Available" ...

[Primary]=[Secondary]
or
[Secondary]=[Tertiary]
or
[Primary]=[Tertiary]

Further thoughts welcomed.

TIA, Bob

[Cognos_Jan2017]

Thank you seb24c.  I just saw your Post from 13 Feb 2020 03:31:55 pm.  I was writing a Post at the same time, and never saw the 13 Feb 2020 03:31:55 pm one until now (I was offline from then until today).

I will try your 03:31:55 pm code to see what occurs, and reply back to this Topic.

[Cognos_Jan2017]

U P D A T E ...

seb24c ... Your recommendation from 13 Feb 2020 WORKS !!!

That most definitely merits an Applaud.

THANK you again, Bob

[seb24c]

Glad to hear it! Not sure why your other attempt didn't work just as well, but at least it's working for you now!