Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only

  • 1.  Data Module Calculation results in multiple rows per object

    Posted 01/17/19 04:24 AM
     
    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?
     
    David Harrop


    #CognosAnalyticswithWatson


  • 2.  RE: Data Module Calculation results in multiple rows per object

    Posted 01/17/19 09:59 AM
    I think this is happening coz of the Left Outer joins .. your Details table is the base table and when you bring in stuff from the other 2 tables you will still see everything from the West_Engage_Details plus whatever matches across the rest 2 tables. Since you are comingling things in that calculation this will carry over. You probably will need a post aggregation filter - as in once we get all the data you need apply the filter for the rows you only need? Its a bit hard to visualize .. here i might be wrong as well or did not understand your problem.

    ------------------------------
    Abhi Majumdar
    ------------------------------



  • 3.  RE: Data Module Calculation results in multiple rows per object

    Posted 01/18/19 05:24 PM
    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
    ------------------------------



  • 4.  RE: Data Module Calculation results in multiple rows per object

    Posted 01/21/19 05:25 AM
    Thanks Denilson,

    I'm afraid that my tables are already in left outer join relationships, with West_Engage_Details on the left of each. 
    Left Outer Join

    ------------------------------
    DAVID HARROP
    ------------------------------

    Original Message


Related Content