Skip to Content
avatar image
Former Member

Count values which appear in 2 columns

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 04, 2018 at 02:19 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Jan 22, 2018 at 03:54 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded