If "West_Deal_Pulse" and "West_Deal_Scan_Offerings" use the PK for West_Engage_Details to reference it in a left-outer join relationship, it is expected that you will have up to two appearances of the same row of West_Engage_Details in the final data set.
From what I understood of your goal "My objective is to read data for West_Deal_Pulse for each object in West_Engage_Details unless there is a row for that object in West_Deal_Scan_Offerings which should then take precedence. ", you could have *West_Engage_Details* be the left side of an outer left join to the other tables, in which case you would see all the columns for both West_Deal_Pulse and West_Deal_Scan_Offerings in the same row as all the columns of their respective respective West_Engage_Details row.
------------------------------
Denilson Nastacio
------------------------------
Original Message:
Sent: 01-17-2019 04:23 AM
From: DAVID HARROP
Subject: Data Module Calculation results in multiple rows per object
Hope someone here can help me with a data module calculation problem I am having which results in multiple rows appearing for one object in a data set built from the data module.
My data module contains three tables:
1. West_Engage_Details (PK = EngDocID) - each row represents a unique object
2. West_Deal_Pulse (FK = EngDocID)- relationship to West_Engage_Details is left outer join, 1 to 1, no filtering
3. West_Deal_Scan_Offerings (FK = EngDocID) - relationship to West_Engage_Details is left outer join, 1 to 1, no filtering
My objective is to read data for West_Deal_Pulse for each object in West_Engage_Details unless there is a row for that object in West_Deal_Scan_Offerings which should then take precedence. My attempt to achieve this is to create various calculations at the module level, such as this:
CRO =
CASE
WHEN ( WEST_ENGAGE_DETAILS.ENGDOCID = WEST_DEAL_SCAN.ENGDOCID AND GR_Source = 'SCAN') THEN ( WEST_DEAL_SCAN_OFFERINGS.CRO_SCAN )
WHEN ( WEST_ENGAGE_DETAILS.ENGDOCID = WEST_DEAL_PULSE.ENGDOCID AND GR_Source = 'Pulse') THEN ( WEST_DEAL_PULSE.CRO )
END
where GR_Source =
CASE WEST_ENGAGE_DETAILS.ENGDOCID
WHEN (WEST_DEAL_SCAN.ENGDOCID ) THEN ('SCAN')
WHEN ( WEST_DEAL_PULSE.ENGDOCID ) THEN ('Pulse')
ELSE ('None')
END
I created a data set from this data module using columns from West_Engage_Details and other calculated values, but when I add CRO to this data set it creates a second row for an object that contains some different values in various columns, which is obviously incorrect.
In the screenshot there should be one row for this object. Adding CRO and Brokerage each add another row for the same object. Revenue is a column from West_Engage_Details, Cost and Profit are calculated.
I'm obviously approaching this incorrectly due to my inexperience. Can anyone recommend a correct method, please?
#CognosAnalyticswithWatson