comparison between 2 data sets where 1 is null

using bo 5.1.2

i want to compare data from 2 different universes where universe 1 holds no data although universe 2 does hold data for the same client. The need is to identify where data exists in a table in universe 2 for a client yet no data is held for the same client in a similar table in universe 1 the relationship between the universes is that a table holding data in universe 2 contains data which relates to the table that does not hold data in universe 1.

in universe 1 we can extract information relating to a client (name id etc.)and the number of dogs the client has but in the table identifying the number of dogs the client has, there is no data.

in universe 2 we can extract limited client information and the names of the 2 dogs.

we want to identify all those clients for whom, in the table in universe 2, the names of the dogs have been identified and there is no data in the table in universe 1.

objects that can be used in both universes include dates and client data (name id, dob etc.). I have tried synchronisation but i need to identify a method that does not retieve a million rows of data from universe 1.

hope i’ve been accurate

cheers.


Decisys :de: (BOB member since 2004-05-21)

Are your two universes accessing different databases? Or can you build one universe, which accesses both tables?


Andreas :de: (BOB member since 2002-06-20)

i think that i’ve cracked it!!

both universes are accessing 1 database. the method used was to search for all clients with dogs ie:

step1

select objects required for universe 1 query 1
condition
description is not null
and description except prompt (“not recorded”)

step 2

  • insert report
  • insert table
  • select “access data in a different way”
  • select universe
  • select objects required for universe 2 query 2
    condition
    description is not null
    and description except prompt (‘not recorded’)

you now have 2 queries from both universes

step 3
select data provider

step 4
link client identifier from universe 1 query 1 to client identifier universe 2 query 2
trick: initially also link other values if possible dob, name description etc.

step 5
select insert report from “Insert”
select insert table
select “use existing data from the document”
select variables required
remove links to other variables like dob, name etc and leave client id only (this keeps columns like description in the report later - which you would later need to filter on and then compare side-by-side)
filter on the description from universe 2

hope this helps

I am however interested on an easier method to do this. like counting descriptions from both queries where query 1 description > 0 and query 2 description 2 is zero (this is not as accurate though because the database could be used where descrption for query 1 is entered multiple times while description for query 2 is entered only) ie if we want to know that john smyth has dogs we enter in the table 1 but to enable the identification of the breed of dogs he has we enter in table 2 - border collies and german shepards

table 1 =
name - john smyth
id - 007
dob - 01/01/01
dogs - yes

table 2
dogs - German shepard
dogs - border collie
dogs - etc.

realistically there is 1 tuple for table 1
and three or more tuples for table 2

cheers


Decisys :de: (BOB member since 2004-05-21)

oops i missed something

between step 1 and 2, you need to do another query

step 1.5

follow same proceedure as step 1 but for query 2

:lol:


Decisys :de: (BOB member since 2004-05-21)

damn sorry forget my last post

I :shock: posted in error


Decisys :de: (BOB member since 2004-05-21)