Compare fields from different queries

building reports desktopintelligence
#1

Say I have a BO report build on 2 queries.

Both of these queries hold a field with the same name, say Cost Center (one query is a universe other query an excel sheet).

Now I need to come up with some sort of check mechanism.
The BO report will give more cost centers then we have in query 2 the excel sheet.

This is all Ok I just want to know what Cost Center are in Query 1 that are not in Query 2 because I have to exclude these cost centers in the Query filter for next runs.

In excel you would do a vlookup or match from 1 sheet in the other sheet.
How would this work in BO?

rpinxt (BOB member since 2019-01-23)

#2

Have you merged the dimensions?

If so you then have three options for displaying them:
=[Cost Centre]
=[Q1].[Cost Centre]
=[Q2].[Cost Centre]

Put all three in one block and you should see the differences.

Mark P :uk: (BOB member since 2003-02-03)

#3

Yes they are merged.

I will give a try what you suggest.

But what I was looking for is some sort of formula/logic that we I have the whole list with Cost Center (the merged one) that in a column next to it something like a “X” will appear if it is only present in 1 query.

That would be a direct solution.
Making an extra block and them do the checking would be a step extra.

rpinxt (BOB member since 2019-01-23)

#4

Filter where [Q2].[Cost Centre] is null.

Mark P :uk: (BOB member since 2003-02-03)

#5

That extra block would not work.
If all 3 cost centers are in it will fill all fields in 3 columns.
If I take merged cost center out it will only show the cost centers that are in both queries.

Is that a logic to put in a variable that you wrote there?

EDIT

So I was trying with this :

=If([Query 1].[Cost Center - Key]<>[Query 2].[Cost Center]) Then "X" Else "Y"

Seems to work however when I pull this variable in, it will not show the line that gets an “X”…
Why would it do that? There are not filters active.

rpinxt (BOB member since 2019-01-23)

#6

What version of Webi are you using? I’ve seen different behaviour down the years as to whether merging treats it as an inner join, full outer, etc.

Mark P :uk: (BOB member since 2003-02-03)

#7

I’m on 4.2 SP 7

rpinxt (BOB member since 2019-01-23)

#8

OK, not used that yet. If you display the three objects in a block as mentioned earlier, what do you see?
I’d expect =[Cost Centre] to have all CCs in both queries and the other two to only have the cost centres that are in their queries, so there would be a reasonable percentage in both then potentially some gaps in both lists too.

Mark P :uk: (BOB member since 2003-02-03)

#9

Nope not happening.

As described (or tried to :slight_smile: ), looks like the merge makes it that when the merged dimension is in all lines will get the number.

When I take the merge dimension out only those will show where cost center number is in both queries.

So the number that are in Q1 but not in Q2 will just not show (similar to what happens with the formula).
And showting it is just what I am after.

rpinxt (BOB member since 2019-01-23)