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.
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.
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.
count ([Field4] for [Field1], [Field2], [Field3]) > 1
Hey BigChris thanks for the answer. I will try it ! 8)