Count values which appear in 2 columns

[Former Member]

Hi, I'm very new to Business Objects, and I'm struggling to work out how to do something.

I have a list of 9 digit ID numbers, but in some circumstances the first digit gets dropped, and in some circumstances the last digit gets dropped. I need to find out how often the first 8 digits of an ID matches the last 8 digits of a different ID. E.g.

1. 123456789 12345678 23456789
2. 987654321 98765432 87654321
3. 012345678 01234567 12345678

So in this circumstance, the first 8 of row 1 matches the last 8 of row 3. I could do with identifying which IDs have matches and also a total number.

In Excel I would have used something like =countif(C:C,B1) so I could see which ones had matches, but I can't find a way to do this in WebI.

How do I count values which do not appear in another column?

[JBARLOW]

If you're reporting off a universe, one possible solution would be to create 2 new objects in the universe

- one returning the 1st eight characters of the ID and the 2nd returning the last 8 characters

Then create two queries in Webi (one for each object) and use an intersect query (I think) to return only the values common between both queries.

[amitrathi239]

try like this.

Sort the values in ascending order and then try with below formula.

=If(Left([ID];8)=Previous(Right([ID];8))) Then 1 Else 0

[Former Member]

Thanks for your answer. Which of the 3 columns would I sort into ascending order? I've tried all 3 but it doesn't seem to work.

There are approx. 2 million rows of data (the main reason I can't use Excel for this) so it's unlikely that, even sorted, one value would match the previous value in the next column.