Data modelling/relationship help

[ROG]

Hi all,

I'm working with Customer Service data, and I need to join the two tables, but I'm not sure how to do that as they don't a same key column.
Call Logs shows info about products and compaints from the customer.

Call Detailed shows the details of the call, such as time the cusotmer called, if the call was abandonned or not or transferred.

If a customer call on the same day for different issue, it will have two records.

The stakeholders told me the way they identify a customer from the call detailed table in the call logs table is using the customer number (Originator column from the Call Detailed).

If the customer called from their own number, they will be found on the CustomerNumber column, but if the customer called from a different number, they will be found on the CustomerCallingNumber column.

My question is: How could I join these two table without creating duplicate values?

Many thanks!

[v-xinruzhu-msft]

Hi @ROG 

You can create a new column in Call Logs table

CustomerNo = SWITCH(TRUE(),[CustomerCallingNumber]=BLANK()&&[CustomerNumber]<>BLANK(),[CustomerNumber],[CustomerCallingNumber]<>BLANK()&&[CustomerNumber]=BLANK(),[CustomerCallingNumber])

Then create the relationship between two tables with the fields "CustomerNo" and "Originator"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.