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.
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