I have two data sources with one common field, but unfortunately they don't have a one-to-many or one-to-one relationship.  I thought that maybe putting one of the queries into a crosstab would make it one-to-many.  But based on the results of the joined query, it is still reading the original source as 'many'.  I'm not sure if that really makes sense to anyone else.  I really just want it to function as a vlookup.  But the left-side of the vlookup has duplicates that I want it to consolidate and then doing the join.

------------------------------
Mark Bruzek
------------------------------

#CognosAnalyticswithWatson

Try with coalesce on all the data items you pull into the join query.

Coalesce( [Q1].[DataItem],[Q2].[DataItem] )

Else i am not sure i understand you q.

------------------------------
Morten Frandsen
------------------------------

You could try to model bridge tables in your model.  You can model them in FM models and CA data modules. 

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_fm.doc/c_dyn_query_bridge_tables.html

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_fm.doc/t_dqm_brdg_tble.html

------------------------------
IAN HENDERSON
------------------------------

Hi Mark,
You can model this either using Data Modules or Framework Manager models, the principle is the same.
I'll use the Data Module Terminology.
The lookup-table in raw format say it's defined as LTRaw(col1, col2, code_col, description, col5, col6 etc).
Select this table and from the context menu select New Table... then Create a View.
Select only the code_col and description. Create the view.
Let's call this table/view LTView.
Select it and from the context menu select Properties... In that panel open up Advanced. Set Usage to Summary.
The data in the grid should now reflect all the unique code,description combinations.
I'm going by the fact that the code field is going to be unique.
In case the code field is not unique
- in the LTView, select the description field
- in the properties, set usage=measure (even though it isn't) and set the aggregate=minimum.
- refresh the grid view. That should now show a single row for each unique code field.
- in CA 11.1.5 you can select the view and look at the generated SQL and see how it does the summarizing.

The data-table in raw format say it is defined as DATARaw(col1, col2, code, col3, col4)
Join from the data-table to the LTView set it as a N on the data-table side and a 0-1 on the LTView side.

The Framework Manager version would be similar, instead of creating a new view you would create a Model Query Subject.
It has a usage that can be set to summary.
You will have to tinker with the aggregate settings to make a single record per code value.
Joins can be made the same way.

//Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Thu February 13, 2020 05:49 PM
From: IAN HENDERSON
Subject: Many-to-many relationship

You could try to model bridge tables in your model.  You can model them in FM models and CA data modules. 

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_fm.doc/c_dyn_query_bridge_tables.html

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_fm.doc/t_dqm_brdg_tble.html

------------------------------
IAN HENDERSON
------------------------------

The solution to my problem ended up being embarrassingly simple.  After enough toying around, I determined that my custom calculation fields did not have the proper detail/summary aggregation settings for the report.  Unfortunately, our internal IT department does not support Cognos as much as they should.

------------------------------
Mark Bruzek
------------------------------

Original Message:
Sent: Thu February 13, 2020 08:37 PM
From: HENK CAZEMIER
Subject: Many-to-many relationship

Hi Mark,
You can model this either using Data Modules or Framework Manager models, the principle is the same.
I'll use the Data Module Terminology.
The lookup-table in raw format say it's defined as LTRaw(col1, col2, code_col, description, col5, col6 etc).
Select this table and from the context menu select New Table... then Create a View.
Select only the code_col and description. Create the view.
Let's call this table/view LTView.
Select it and from the context menu select Properties... In that panel open up Advanced. Set Usage to Summary.
The data in the grid should now reflect all the unique code,description combinations.
I'm going by the fact that the code field is going to be unique.
In case the code field is not unique
- in the LTView, select the description field
- in the properties, set usage=measure (even though it isn't) and set the aggregate=minimum.
- refresh the grid view. That should now show a single row for each unique code field.
- in CA 11.1.5 you can select the view and look at the generated SQL and see how it does the summarizing.

The data-table in raw format say it is defined as DATARaw(col1, col2, code, col3, col4)
Join from the data-table to the LTView set it as a N on the data-table side and a 0-1 on the LTView side.

The Framework Manager version would be similar, instead of creating a new view you would create a Model Query Subject.
It has a usage that can be set to summary.
You will have to tinker with the aggregate settings to make a single record per code value.
Joins can be made the same way.

//Henk

------------------------------
HENK CAZEMIER
------------------------------


Original Message:
Sent: Thu February 13, 2020 05:49 PM
From: IAN HENDERSON
Subject: Many-to-many relationship

You could try to model bridge tables in your model.  You can model them in FM models and CA data modules. 

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_fm.doc/c_dyn_query_bridge_tables.html

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_fm.doc/t_dqm_brdg_tble.html

------------------------------
IAN HENDERSON