Forum Discussion

emma313823's avatar
emma313823
Icon for Helper V rankHelper V
9 years ago

Creating many to many relationship

Hi All

 

I have a dashboard I'd like to create using two exported excel spreadsheets.

 

Export 1:

has a unique customer ID the software creates

we have had a custom field added which allows us to add the unique customer ID from Export 2

This data has quotes and new business opportunities  (NBO) detail

 

Export 2:

has a unique assigned customer ID

This data has actual sales data

 

There could be many lines of quotes or NBOs in Export 1, so the customer ID could be listed multiple times for each line of data.  Export 2 has the same issue...the customer ID is listed multiple times for each line of sales data.

 

How can I create a relationship between these two files, so I can create a dashboard that will yield data properly....ie when an account is selected for visuals it would show accurate data from both files?

 

Emma

 

The issue

Need Help

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    9 years ago

    Hi Emma,

     

    Don't you have customer master table which you can bring to your data model and create relationship with customer master wiht two exported tables.

     

    If not then we can create customer master table from these two tables in query and create relationship with this 3rd extracted tables with exported table.

     

    In nutshell, you need customer master table, if you already have, great otherwise , we need to create it. Let me know if you need help with creation of customer master if it doesn't already exists in your database.

     

    Thanks,

    P

    • emma313823's avatar
      emma313823
      Icon for Helper V rankHelper V
      9 years ago

      I can obtain a customer master table - but each export is coming from two different systems....so the the ID for customer ABC will actually have a differnet ID from each table?  How would I make that work with two differnet customer master tables?

       

      Emma

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        9 years ago

        Basically you are saying there are seperate customer master table for the exports, and those customer master has different ids for the same customer, correct?

         

        But one thing you mentioned that in your export 1, you have customer id which also exists in Export 2. Having said that, don't you think that customer master for export 2 will work, because that customer master id is in both the export files.

         

        Sorry if I misunderstood your question.

  • Baskar's avatar
    Baskar
    Icon for Resident Rockstar rankResident Rockstar
    9 years ago

    Hi emma ,

     

    For achieve this u have to create one intermediate table called factless fact table.

     

    so create one calculate table with only unique customer ID.

     

    like 

    Calculated table = Values("Customer ID from Table")

     

    if customer coming from two different table no problem create one more  table and union this two table as one table.

     

    let me know any question