on 01-04-2018 9:35 AM
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.
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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.