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