If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Returning Unique entries

Started by gronhoffga, 05 Mar 2012 08:23:46 AM

Previous topic - Next topic

gronhoffga

Good morning everybody,
I am using RS in C10 and need to do the following:

I have two fields A & B both contain serial numbers from two separate systems, however serial numbers from both can exist in the other, for example

field A serial # 12345
                             123456
                             09876

filed B serial # 12345
                            43567
                            86452

Now I need to pull back ONLY the unique entries from either A or B (separate reports) so in this case serial number 12345, since it exists in both is not needed.

I have already made several attempts but either get everything or nothing :(

case
when [serial number a] not contains ([serial number b]) then [serial number a]
end

also tried

case
when [serial number a] <> ([serial number b]) then [serial number a]
end

Any assistance would be appreciated!

thanks

graham

Lynn

Is your source relational? What do you mean by separate reports? Are you querying the two systems separately with two separate queries in RS or is there a single table in your database with data from the two systems merged that allows you to create a single query with A and B on the same row?

Although I don't exactly understand how your data is structured my thought is to consider a union query and leave the "Duplicates" property set to "Remove". One query for A and another query for B when unioned will give you a distinct list of serial numbers.

This would still include 12345 but it would appear only once. Do you really need to suppress that value from the result?

gronhoffga

I am using a relational data source, my data is coming from two separate queries that I joined in Framework Manager. To explain it further, we have two systems that track on serial numbers, one internal and one external. When new equipment comes in the info is put in our internal system and then synced with the external on a set schedule. Because the system is new we want to make sure all the gaps a filled in, so if serial number 12345 is in the internal and not the external then we need to know that, if it is in both then we don't care about it since it has been synced properly.

Lynn

How are they joined? On serial number?

gronhoffga


Lynn

Won't that only ever give you the ones that are properly synced?

gronhoffga

I am doing the join to link the tables then in RS I am perforning the following case:

case
when [DPASS Serial Number] = [CompSys Serial Number] then [DPASS Serial Number]
end

doing the above returns only items that are present in both tables.

Lynn

I think you are getting only items present in both systems because of your join in FM, not the case statement in your report.

If you do an join in FM such as DPASS.serialNumber = CompSys.serialNumber .... think about it .... what will that give you?

You will only get a result set that includes items existing in both tables and effectively eliminate all your sync issues.

You need an outer join or a union in FM to do what you are describing. If serial numbers can be sometimes in DPASS but not in CompSys as well as the reverse, then you are looking at a full outer join. I think a union might be preferable in that situation.

I am prone to huge lapses of rational thought, so set me straight if I am misunderstanding  :o

gronhoffga

No that makes sense, I am going to give the outer join a try and if that doesent work then I will try the union. I will post back after I am done.

Thanks for the help

g

Lynn

Good luck!

If you specify DPASS 1..0 CompSys then you are saying give me everything in DPASS whether or not there is a match in CompSys.

If you specify CompSys 0..1 DPASS then you are saying give me everything in CompSys whether or not there is a match in DPASS.

Either approach above implies that you have ALL serial numbers in one of the systems and only SOME of the serial numbers in the other. If neither system has every serial number then the above type of outer join won't give you the proper result.

Cardinality of 1 vs. n indicates dimensions vs. facts, so I'm not sure what is appropriate in your case.

Galadin

This is sample, and you don't have to modify your framework:

1.  Create two queries, one for each table, and make sure it ONLY pulls fields from that table.
2.  Link the two queries in Query Explorer using an Except (theta join)
3.  In the new query, pull the fields from Query A

Lynn

Quote from: Craig Linderoth on 08 Mar 2012 12:01:16 PM
This is sample, and you don't have to modify your framework:

1.  Create two queries, one for each table, and make sure it ONLY pulls fields from that table.
2.  Link the two queries in Query Explorer using an Except (theta join)
3.  In the new query, pull the fields from Query A

True, the join can be done in the report, but based on what has been described it sounds like the FM join currently there is just plain wrong and should be corrected.

Further, a join on the report side will involve local processing to combine the two result sets which may not perform well in a high volume situation.

Still, very good of Craig to point out that a join within the report is a perfectly good approach to consider.