Need Join Help

[CashmereFarm]

This sounds simple, but I can't seem to get it to work in Report Studio.  See example below.  I have two tables and I need to find Table 1 rows that do not have a matching "Period" in Table 2 and the result should show the Table 1 "ID" with the Table 2 "Period" row that is missing.  (I don't care if there are rows in Table 1 that do not have a matching "Period" in Table 2.)

For example:
Table 1
ID = 001 Period = 00
ID = 001 Period = 02
ID = 002 Period = 00
ID = 002 Period = 04

Table 2
Period = 00
Period = 01
Period = 04

Expected Results:
ID = 001 Period = 01
ID = 001 Period = 04
ID = 002 Period = 01

I'm thinking this is a join problem.

Suggestions, please.

[blom0344]

It is not a join problem, since joins are not supposed to come up with missing combinations.

To put it into words:

You want the complement of combinations that the join would provide.

The solution is extremely simple in SQL:

(SELECT T1.ID,T2.PERIOD
FROM T1 CROSS JOIN T2)
EXCEPT
(SELECT T1.ID,T1.PERIOD
FROM T1)

Cognos allows you to build construct using Except and you can allow for the cross-join.
(But you would need to have T1 and T2 'unjoined' for the cross-join to be produced)

[Suraj]

What's your join condition?
if you just want the periods from table 2, apply a 1..1 relationship.

[CashmereFarm]

To Reply #1:  Thank you for the information, but I only have Report Studio available to me.  Any ideas, even if "off the wall"?

To Reply #2:  You don't understand the problem.  If it was a simple 1:1 join, I would have done that.  See the example provided. 

One idea I had was to somehow add rows to Table 1 that would allow for all Periods for each Id along with an indicator that these added rows are "missing".  Then I could join Tables 1 and 2 and on an equal join I would know this was a missing row.  The problem I have is with the "somehow" part.  Like I said I only have Report Studio available but "off the wall" is okay with me.

[Suraj]

Okay, I didn't read the post, just looked at the output sample and suggested.
Here it goes.
What you need is a join but not on any of the data items.
First rename data items period and id so that you know what query they belong to.
q1:
period-q1
id-q1

q2:
period-q2
id-q2

It could be data item join as well but it has to be 0..n in both ways because if you have 1..1 it'll filter out rows.
Or, create a  join key data item in both queries with expression '1'.
Join this join key in q1 to join key in q2.

Now you have all in one query.
Then create data items for your report.

ID:
case
when period-q1 <> period-q2 then id-q1
else null
end

Period:
case
when period-q1 <> period-q2 then period-q2
else null
end

see if that brings desired result.
can't test myself as i'm out right now.

[CashmereFarm]

Thanks. I tried this and it doesn't give the expected results.  When I join on that "dummy" field = '1', it goes thru each row of both tables and produces multiple rows with values that are not correct.  I think you're on to something though, this is close.

[blom0344]

Thanks. I tried this and it doesn't give the expected results.  When I join on that "dummy" field = '1', it goes thru each row of both tables and produces multiple rows with values that are not correct.  I think you're on to something though, this is close.

Using a fixed value (like 1 in a dataitem as Suraj proposed) on both ends of the join should effectively yield the crossjoin that I mentioned.
The output should yield all possible combinations.
If you take up the EXCEPT construct then you should have get the missing combinations..

[CashmereFarm]

Could you pls explain more about the "except" statement.  Where is this placed?  I don't normally use SQL, directly, in Report Studio.  Thanks!!

[blom0344]

You do not need to use SQL directly, you can simply open query explorer --> manage queries.
In the left part of the GUI is a complete set of insertable objects (Join,Union,Intersect , Except)

[CashmereFarm]

Sorry, but it is marking rows that are *Not* missing as if they *are* missing and I think it is because of the 0.n join creating all those extraneous rows for every value.

[blom0344]

Cashmere,

I took your example and build it in test. Works like a charm   ;D
If you send me a mail, then I can return screenshots and definition.

added 09/17/2009:

example based on GO Sales and Retailers:

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/2.0/" expressionLocale="en"><!--RS:8.1-->
<modelPath>/content/package[@name='GO Sales and Retailers']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Country code" aggregate="none"><expression>[gosales_goretailers].[Countries].[Country code]</expression></dataItem><dataItem name="Country" aggregate="none"><expression>[gosales_goretailers].[Countries].[Country]</expression></dataItem><dataItem name="join1" aggregate="none"><expression>1</expression></dataItem></selection>
</query>
<query name="Query2">
<source>
<model/>
</source>
<selection><dataItem name="Country" aggregate="none"><expression>[gosales_goretailers].[Countries].[Country]</expression></dataItem><dataItem name="join2" aggregate="none"><expression>1</expression></dataItem></selection>
</query><query name="Query3">
<source>
<joinOperation>
<joinOperands>
<joinOperand cardinality="1:N"><queryRef refQuery="Query1"/></joinOperand>
<joinOperand cardinality="1:N"><queryRef refQuery="Query2"/></joinOperand>
</joinOperands>
<joinFilter>
<filterExpression>[Query1].[join1] = [Query2].[join2]</filterExpression>
</joinFilter>
</joinOperation>
</source>
<selection><dataItem name="Country code"><expression>[Query1].[Country code]</expression></dataItem><dataItem name="Country"><expression>[Query2].[Country]</expression></dataItem></selection>
</query>
<query name="Query4">
<source>
<model/>
</source>
<selection><dataItem name="Country code" aggregate="none"><expression>[gosales_goretailers].[Countries].[Country code]</expression></dataItem><dataItem name="Country" aggregate="none"><expression>[gosales_goretailers].[Countries].[Country]</expression></dataItem></selection>
</query>
<query name="Query5">
<source>
<queryOperation name="Except1" setOperation="EXCEPT">
<queryRefs><queryRef refQuery="Query3"/><queryRef refQuery="Query4"/></queryRefs>
<projectionList autoGenerated="true"><queryItem name="Country code"/><queryItem name="Country"/></projectionList>
</queryOperation>
</source>
<selection><dataItem name="Country code"><expression>[Except1].[Country code]</expression></dataItem><dataItem name="Country"><expression>[Except1].[Country]</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page2">
<pageBody class="pb">
<contents>

<table class="tb"><tableRows><tableRow><tableCells><tableCell><contents><list class="ls" refQuery="Query1" rowsPerPage="50">



<style>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Country code"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Country code"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Country"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Country"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents><style><CSS value="width:5cm"/></style></tableCell><tableCell><contents><textItem><dataSource><staticValue>21 countries (code 15 is missing)</staticValue></dataSource></textItem></contents></tableCell></tableCells></tableRow></tableRows><style><CSS value="border-collapse:collapse;width:100%"/></style></table></contents>
</pageBody>
<pageHeader class="ph">
<contents>
<block class="ta">
<contents>
<textItem class="tt">
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter class="pf">
<contents>
<table class="tb">
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfDate()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<staticValue>- </staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<reportExpression>PageNumber()</reportExpression>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue> -</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfTime()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page><page class="pg" name="Page1">
<pageBody class="pb">
<contents>

<table class="tb"><tableRows><tableRow><tableCells><tableCell><contents><list class="ls" refQuery="Query5">



<style>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Country code"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Country code"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Country"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Country"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents><style><CSS value="width:5cm"/></style></tableCell><tableCell><contents><textItem><dataSource><staticValue>For each code 20 countries; the correct combination of code/country is deselected through the except construction</staticValue></dataSource></textItem></contents></tableCell></tableCells></tableRow></tableRows><style><CSS value="border-collapse:collapse;width:100%"/></style></table></contents>
</pageBody>
<pageHeader class="ph">
<contents>
<block class="ta">
<contents>
<textItem class="tt">
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter class="pf">
<contents>
<table class="tb">
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfDate()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<staticValue>- </staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<reportExpression>PageNumber()</reportExpression>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue> -</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfTime()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
</report>

[/size]