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
Original Message:
Sent: Thu February 13, 2020 02:25 PM
From: Mark Bruzek
Subject: Many-to-many relationship
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