BusinessObjects Board

Checking if value exists in more than one DP

building reports desktopintelligence
#1

Sorry if this is basic, but it is Friday afternoon, my brain isn’t working well (and I couldn’t find anything while searching).

I have two DPs based on excel spreadsheets. I’m trying to figure out how to display values that exist in one but not the other.

Ex.


DP1:                                                 DP2:
1                                                      2
2                                                      4
3                                                      6   
4                                                      3
5                                                      1

Using the data above, I’d like to return 5 and 6. :crazy_face:

Jeff D (BOB member since 2005-06-03)

#2

I’m not sure if a combined query works with personal data files, but if it does, that’s the way to go. Use a minus combined query, with the DP that has the values first and minus the DP that is missing them.

I just looked back at your message and you’ll probably have to run two combined minus queries to get the values that are in DP1 and not DP2 and then another minus query to get the values that are in DP2 and not DP1.

Debbie Kelly :us: (BOB member since 2002-08-15)

#3

I think a variation on this technique might work. That technique is to find common items, but you should be able to reverse the logic.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#4

If you use tables instead of EXCEL sheets, I suppose your SQL query should be something like this

SELECT TABLE1.COLUMN
FROM TABLE1
WHERE TABLE1.COLUMN NOT IN ( SELECT  TABLE2.COLUMN FROM TABLE2)
UNION
SELECT TABLE2.COLUMN
FROM TABLE2
WHERE TABLE2.COLUMN NOT IN ( SELECT TABLE1.COLUMN FROM TABLE1)

From the First you will get 5 and from the Second you will get 6.

Hope this will give you some idea. :smiley:

BO_Chief :us: (BOB member since 2004-06-06)

#5

With all due respect, I think we’re off track here. The question was regarding two linked spreadsheets as data providers. I don’t see how SQL solutions apply.

Take a look at the attached example. There are two data providers whose contents match your sample data. The key is applying a filter to a non-linked object from each data provider.

One report block displays all data (not filtered).
One report block displays only common dimensions … filter = Not IsNull(<M(DP1)>) And Not IsNull(<M(DP2)>)
One report block displays only not common dimensions … filter = IsNull(<M(DP1)>) Or IsNull(<M(DP2)>)
Multi DP filtering.rep (45.0 KB)

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#6

As Dwayne infers, the trick is to add a dummy measure to each data provider and filter on that.

anorak :uk: (BOB member since 2002-09-13)

#7

Thanks for all your help, and Dwayne than you for taking the time to create that sample report. It’s all crystal clear to me now!

Jeff D (BOB member since 2005-06-03)