BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

[BOBJ 4.x] Compare fields from different queries


 
Search this topic... | Search Desktop Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Author Message
rpinxt
Senior Member
Senior Member



Joined: 23 Jan 2019

Posts: 76



PostPosted: Mon Dec 02, 2019 6:17 am 
Post subject: Compare fields from different queries

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?
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11460
Location: Manchester, UK


flag
PostPosted: Mon Dec 02, 2019 8:42 am 
Post subject: Re: Compare fields from different queries

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.

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
rpinxt
Senior Member
Senior Member



Joined: 23 Jan 2019

Posts: 76



PostPosted: Mon Dec 02, 2019 8:46 am 
Post subject: Re: Compare fields from different queries

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.
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11460
Location: Manchester, UK


flag
PostPosted: Mon Dec 02, 2019 8:47 am 
Post subject: Re: Compare fields from different queries

Filter where [Q2].[Cost Centre] is null.
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
rpinxt
Senior Member
Senior Member



Joined: 23 Jan 2019

Posts: 76



PostPosted: Mon Dec 02, 2019 8:52 am 
Post subject: Re: Compare fields from different queries

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 :
Code:
=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.
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11460
Location: Manchester, UK


flag
PostPosted: Mon Dec 02, 2019 9:15 am 
Post subject: Re: Compare fields from different queries

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.
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
rpinxt
Senior Member
Senior Member



Joined: 23 Jan 2019

Posts: 76



PostPosted: Mon Dec 02, 2019 9:18 am 
Post subject: Re: Compare fields from different queries

I'm on 4.2 SP 7
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11460
Location: Manchester, UK


flag
PostPosted: Mon Dec 02, 2019 10:00 am 
Post subject: Re: Compare fields from different queries

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.

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
rpinxt
Senior Member
Senior Member



Joined: 23 Jan 2019

Posts: 76



PostPosted: Mon Dec 02, 2019 10:13 am 
Post subject: Re: Compare fields from different queries

Nope not happening.

As described (or tried to icon_smile.gif ), 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.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0278 seconds using 17 queries. (SQL 0.0021 Parse 0.0009 Other 0.0249)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo