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

Full outer join question

Started by terry_stjean, 21 Aug 2007 12:41:21 PM

Previous topic - Next topic

terry_stjean

I have a report when I have to create 2 queries for the report to respresent 2 columns.
The first query is Customer, Item, Date, Measure 1.
The second query is Customer, Item, Date, Measure2.
Data for a Customer/Item/Month can exists in one or both queries.
In the join, I can specify a 0..n to 0..n cardinality to hopefully achieve the full outer join.
So, first, will this solution work or is there a better one.
Second, on the report, which Customer and Item do I use. the one from the first query or the second or does it matter.

Terry

Suraj

If the tables are joined in FM, you can simply drag and drop measure2 along side of measure1.

But if you want to join two queries, if all customers in table1 also exist in table2, you may be able to 1:1 on customer id.
You'll need outer join if some customers are not in one table but exist in other.

terry_stjean

What I'm trying to accomplish is as follows:
Table 1
Product     Sales
A                100
B                150

Table 2
Product      Inventory
A               1000
C               1200

Report
Product      Sales     Inventory
A               100         1000
B               150
C                             1200

I can create 2 queries and then join them, selecting all rows from both tables.
But which Product field do I use in my joined query? If I use the Product from Table 1, then won't it show null for Product C?

Terry

Suraj

I had a similar situation like this before.
I used a calculation item that checks for that.
For example:
Instead of Product use the following or similar logic.
if (table1.Product is not null)
then (table1.Product)
else (table2.Product)

This will use table1 product as long as they exist, if not it'll use table2 product.