How to exclude duplicates in a column based off value in another column

[pjcc.39]

Hi all,

I do not really have much of a technical background on this and would really appreciate your help.

Column 1     | Column 2
111             |A
111             |B
111             |C
222             |B
222             |C
333             |A
333             |C

In the above Cognos List Report, how do I filter out the unique identifiers in column 1 which include "B" in column 2 (i.e. 111 and 222)?

I would like a result where any numerical identifiers in column 1 which has a "B" in column 2 to be excluded, showing only 333 as per below (given both 111 and 222 have "B" associated):
Column 1     | Column 2
333             |A
333             |C

Thank you!

[MFGF]

Hi all,

I do not really have much of a technical background on this and would really appreciate your help.

Column 1     | Column 2
111             |A
111             |B
111             |C
222             |B
222             |C
333             |A
333             |C

In the above Cognos List Report, how do I filter out the unique identifiers in column 1 which include "B" in column 2 (i.e. 111 and 222)?

I would like a result where any numerical identifiers in column 1 which has a "B" in column 2 to be excluded, showing only 333 as per below (given both 111 and 222 have "B" associated):
Column 1     | Column 2
333             |A
333             |C

Thank you!

Hi,

You could take the approach that counts 1 for a 'B' value in Column 2, then filters out Column 1 values that have a maximum flag value greater than 0 in Column 2

eg

maximum(if ([Column 2] = 'B') then (1) else (0) for [Column1]) = 0

Does this work for you?

Cheers!

MF.

[dougp]

This was a lot harder in Cognos than in straight SQL.  Still, only about 10 minutes.

https://pastebin.com/GaKPt5Cv

[pjcc.39]

Hi,

You could take the approach that counts 1 for a 'B' value in Column 2, then filters out Column 1 values that have a maximum flag value greater than 0 in Column 2

eg

maximum(if ([Column 2] = 'B') then (1) else (0) for [Column1]) = 0

Does this work for you?

Cheers!

MF.

Thanks both,

As I am a complete novice wasn't quite sure how to use your solution @dougp, but @MF's answer worked a treat!

However, I did run into a separate issue where when I add a new data item column to the list (not a filter), the number of rows reduce significantly in the report.

e.g. I have created the below list using filters:
ID    | Name
1      | A
2      | B
3      | C
4      | D
5      | E

From here I add a 3rd column, data item called Variable, and the resulting table looks like:
ID    | Name  | Variable
3      | C        | No
4      | D        | Yes

The above I'm guessing resulted as there were no "Variable" data found/assigned (null?) for IDs 1, 2 and 5.
Instead, I would like these null results to show, so that the list is complete as per below:
ID    | Name  | Variable
1      | A        | Null
2      | B        | Null
3      | C        | No
4      | D        | Yes
5      | E        | Null

Can this be done?

Many thanks!

[MFGF]

Thanks both,

As I am a complete novice wasn't quite sure how to use your solution @dougp, but @MF's answer worked a treat!

However, I did run into a separate issue where when I add a new data item column to the list (not a filter), the number of rows reduce significantly in the report.

e.g. I have created the below list using filters:
ID    | Name
1      | A
2      | B
3      | C
4      | D
5      | E

From here I add a 3rd column, data item called Variable, and the resulting table looks like:
ID    | Name  | Variable
3      | C        | No
4      | D        | Yes

The above I'm guessing resulted as there were no "Variable" data found/assigned (null?) for IDs 1, 2 and 5.
Instead, I would like these null results to show, so that the list is complete as per below:
ID    | Name  | Variable
1      | A        | Null
2      | B        | Null
3      | C        | No
4      | D        | Yes
5      | E        | Null

Can this be done?

Many thanks!

Hi,

Apologies, I'm not quite following the exact steps you took here. Did the filter change when adding the Variable column, and/or is the Variable column referenced in the filter at all?

Cheers!

MF.

[dougp]

wasn't quite sure how to use your solution

It's a Cognos report spec.  You paste it into Cognos.

New | Report
OK
More (...) | Open report from clipboard
Paste the report spec
OK