Cognos Analytics

 View Only
Expand all | Collapse all

Want to join 2 tables that have no common unique indexes

  • 1.  Want to join 2 tables that have no common unique indexes

    Posted Thu February 11, 2021 05:36 PM
    Hi all,
         I want to join 2 tables that have no common/unique indexes.  They do both have the field/column "site number", but they both have multiple of the same site numbers.  Do I perhaps need to create a 3rd table with just the "site number" with unique site numbers and do a 1-to-many to both of the other tables which I think would be a many-to-many?

    Jim

    ------------------------------
    Jim Denton
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Want to join 2 tables that have no common unique indexes

    Posted Fri February 12, 2021 08:59 AM
    with this example data, what are you hoping for the output result set?

    table1
    sitenum,name,
    1,ben
    1,henry
    2,frank
    3,beth
    4,olivia

    table2
    sitenum,city
    1,miami
    1,atlanta
    2,portland
    3,seattle
    5,acron

    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------



  • 3.  RE: Want to join 2 tables that have no common unique indexes

    Posted Tue February 16, 2021 12:22 AM

    I think you need to create a data item named "CJ" in both tables with the value 1.  Then both tables will have a common value to join them.  Of course, since the common item is manufactured the relationship will be many-to-many..  It's not clear if you need to join them using site number which could be a common field



    ------------------------------
    Mauro Santos Otero
    ------------------------------