If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Cognos list keep rows with aimilar column values

Started by sanchoniathon, 11 Dec 2020 11:51:47 AM

Previous topic - Next topic

sanchoniathon

Hi,

Can someone please help me understand how to do the following in cognos:

Keep certains rows based on values in certain columns. The almost duplicates i would call them.

Row#1 : A  |  2  | 200 | 123
Row#2 : A  |  2  | 200 | 456
Row#3 : A  |  2  | 200 | 789

Row#4 : A  |  2  | 333 | 321

Row#5:  B  |  1  | 100 | 444
Row#6 : B  |  2  | 200 | 352
Row#7:  B  |  4  | 200 | 235
Row#8:  B  |  1  | 100 | 442

We want to keep only rows:
#1, 2, 3, 5 and 8

Thank you all.

MFGF

Quote from: sanchoniathon on 11 Dec 2020 11:51:47 AM
Hi,

Can someone please help me understand how to do the following in cognos:

Keep certains rows based on values in certain columns. The almost duplicates i would call them.

Row#1 : A  |  2  | 200 | 123
Row#2 : A  |  2  | 200 | 456
Row#3 : A  |  2  | 200 | 789

Row#4 : A  |  2  | 333 | 321

Row#5:  B  |  1  | 100 | 444
Row#6 : B  |  2  | 200 | 352
Row#7:  B  |  4  | 200 | 235
Row#8:  B  |  1  | 100 | 442

We want to keep only rows:
#1, 2, 3, 5 and 8

Thank you all.

Hi,

Can you articulate the logic used to decide when to keep a row and when not to? I'm not seeing any pattern from the sample you posted?

Cheers!

MF.
Meep!

sanchoniathon

#2
Hi,

If it wasn't for the 4th columns ROWS# 1,2 and 3 would of appear in 1 single line.

As for the Row#4, we do not want it because it has a different value under 3rd column and a different value under the 4th column.

In other words since first row has it's 1fst column value, 2nd column value and 3rd column value REPEAT on other rows then we want the rows#1, 2 and 3.

We don't want Row#4 BECAUS IT IS NOT REPEATING (it's 1fst, 2nd 3rd column's values) on any other row.

Lastly, since thw only rows that seem to have repeated values in their colum 1, column 2 and column 3 are rows5 and 8 then we want them.

So looking to keep rows where their first 3 columns have same values.

BigChris

count ([Field4] for [Field1], [Field2], [Field3]) > 1

sanchoniathon

Hey BigChris thanks for the answer. I will try it !  8)