Forum Discussion

ROG's avatar
ROG
Responsive Resident
3 years ago

Data modelling/relationship help

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!

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.