Creating many to many relationship

[emma313823]

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

[Baskar]

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 

[parry2k]

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]

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]

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.

[emma313823]

Yes to both...

example

Export 1, customer name EMC Corporation with Sales Logix ID of AZ7TN0002516, customer field included in our CRM software allows us to enter the customer ID from Export 2 of 65182.

Export 2, customer name EMC Corporation, customer ID of 65182

To your point of using export 2 as the master table...If I have a master customer list - this will have only one instance of a customer name/ID.  So you are saying using this I can point the customer ID to both export 1 and export 2 and establish the needed connection?  Do I understand that correctly?

Emma

[parry2k]

Sorry away from my desk, yes customer master from export 2 will work and both exports will have relation to this master table.