Dulicate value in column

[ari_meta]

Hi All

  I am in a strange situation in cognos. In one of the column in database. Data is like this-   A,A,B,C,D,D
User want to see it  like this-  A,B,C,D
In other words, if values are repeating in the string. They should be eliminated. I tried  everything, I could have thought of.
If any one of you can help. It will be appreciated.
Thanks
Ari

[CognosPaul]

Do you mean that the data is occasionally doubled inside the same field, or there are multiple rows with the same values? If there are multiple rows, is itt because of other FKs inside the table? Cognos should automatically group by every field in a query, unlesss auto aggregation has been turned off. Can you go into more detail?

[MFGF]

If you mean that this is a delimited string held within a single database column, then you will have your work cut out for you. Relational databases (and therefore SQL) were originally designed to host normalized data, and an "array" like this doesn't even conform to first normal form - the first step in normalizing data. Consequently, SQL (and therefore Cognos BI tools) are not inherently designed to make extraction of data like this an easy task.

If you have the facility to do this, your best bet would be to restructure the underlying data to pivot this "array" into separate rows in the table, each having a single value in the column. Cognos would then automatically group on this in a report and you should not see duplicates.

If you don't have this luxury, you will need to write some very messy calculations to extract each value and check if it is already in the string, then build a new string with just the distinct values in it. It will not be an easy task, though.

Regards,

MF.

[CognosPaul]

A UDF would probably be good here. There are plenty of examples of string splitters, this might be helpful:
http://stackoverflow.com/questions/8566371/find-unique-values-in-a-column-of-comma-separated-text

[ari_meta]

Thanks all for your replies.

As, i was assuming. it is very difficult task to do from cognos side. We need to create some SP or UDF in database and break that string and compare the results.

If i am correct, we can not create any for/while loop like condition in cognos. can we ?

Thanks  Again

Ari

[MFGF]

Hi,

Not in Cognos BI directly, no. If you are using Data Manager you have these kinds of facilities available there...

Regards,

MF.